﻿'Description:批次检验类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2017-10-31
'Modify by Yuxj20181108 IssueErpMaterial 表增加 isChange
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class uLMSIssueERPMaterialbusiness
    Implements IBusiness.uLMSIssueERPMaterialIbusiness

    'Add by Yuxj20180620
    Private m_ContainerObj As New uLMSContainerBusiness

    Private Function GetFactoryList() As DataTable Implements uLMSIssueERPMaterialIbusiness.GetFactoryList
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM Factory f order by f.factoryname asc")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' yuxj20190620处理任务单生成时，因异常导致批次开立但enginepartplan id没有更新进去的情况
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetTaskExceptionData(ByVal mfgid As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetTaskExceptionData
        Dim sb As New StringBuilder
        sb.AppendFormat(<string>SELECT ep.containername,ep.containerno,c.containerid,ep.productid,
        NVL(pb.revofrcdid,m.productid) mproductid ,c.mfgorderid cmfgorderid ,ep.mfgorderid,c.togetherflag 
        FROM enginepartplan ep 
        INNER JOIN container c ON ep.containername=c.containername 
        left join mfgorder m ON m.mfgorderid = ep.mfgorderid
        LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid 
       WHERE  ep.mfgorderid='{0}' and nvl(ep.containerid,ep.enginecontainerid) IS NULL AND c.mfgorderid=ep.mfgorderid
        AND ep.containerno=c.togetherflag </string>.Value, mfgid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 20190619yuxj配套查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIssueMaterialQuery(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetIssueMaterialQuery
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT u.attributevalue, cm.mfgordername,im.containerno,cm.productname, ")
        sb.AppendLine("cm.productbatchno,im.issueqty,decode(im.ischange,'0','已配套','1','已取消',im.ischange) ischange ,pm.firstassembleqty,jsm.t_wlms description ")
        sb.AppendLine("FROM issueerpmaterial im ")
        sb.AppendLine("INNER JOIN enginepartplan ep ON im.containerid=ep.containerid ")
        sb.AppendLine("LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid ")
        sb.AppendLine("LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机' ")
        sb.AppendLine("LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
        sb.AppendLine(" left join jbst_sync_mes jsm on jsm.t_code=cm.productname ")
        sb.AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("LEFT JOIN mfgorder m ON ep.mfgorderid=m.mfgorderid ")
        sb.AppendLine("WHERE ")
        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" wf.factoryid='{0}'", para("FactoryId"))
        End If

        If para.ContainsKey("TaskBill") Then
            sb.AppendFormat(" and im.ContainerNo like '%{0}%'", para("TaskBill"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%'", para("MachineStart"))
        End If

        If para.ContainsKey("MfgOrder") Then
            sb.AppendFormat(" and m.mfgordername like '%{0}%'", para("MfgOrder"))
        End If

        If para.ContainsKey("Status") Then
            If para("Status") = "已配套" Then
                sb.AppendLine(" and im.ischange='0' ")
            Else
                sb.AppendLine(" and im.ischange='1'  ")
            End If
        End If
        sb.AppendLine(" Order by u.attributevalue, cm.mfgordername,im.containerno,cm.productname, ")
        sb.AppendLine("cm.productbatchno asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' add by yuxj 20171205 获取任务单资料
    ''' Modify by Yuxj20180509
    ''' Modify by Yuxj20180521 只显示任务单最早没有报工完成的阶段批次，并且批次对应工艺必须有Bom
    ''' Modify by Yuxj20180905 将未生成的逻辑更改，由 im.containerid==> e.containerid
    ''' yuxj20221201优化sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetIssueERPMaterial(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetIssueERPMaterial
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                 SELECT    e.workflowdesc wfdesc,e.machineno attributevalue, e.containerid,e.containername,e.mfgordername,e.mfgorderid,e.productname,e.productdesc description, 
                           e.productver ProductRevision,e.workflowver WorkflowRevision,e.workflowname,e.containerno,e.containerno togetherflag,
                           decode(e.containerid||e.enginecontainerid,null,'未生成','已生成') TaskBillStatus,e.bigsmallbomid bomid,e.workflowstepid,e.workflowid,
                           e.workflowstepname,to_char(e.plannedstartdate,'yyyy-MM-dd HH24:mi') plannedstartdate, to_char(e.plannedfinishdate,'yyyy-MM-dd HH24:mi') plannedfinishdate
                           ,me.productfamilyname,me.engineproductname mproductname
                           ,'正常' WORKFLOWTYPE
                    FROM enginepartplan e 
                    left join MFG_ENGINE_DATA me ON me.mfgorderid=e.mfgorderid
                    left join mfgorder m on e.mfgorderid=m.mfgorderid
                    INNER JOIN (                
                                SELECT ep.containername, MIN(ep.workflowstepid) workflowstepid FROM enginepartplan ep
                              LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid                    
                              WHERE ep.usecompleted='0' AND ep.containerno IS NOT NULL                    
                              GROUP BY ep.containername                    
                              ) EP on ep.containername=e.containername and ep.workflowstepid=e.workflowstepid                  
                           LEFT JOIN (select containerno,containerid,count(1) from IssueERPMaterial where ischange='0'
                           group by containerno,containerid ) im ON e.containerid=im.containerid    
                 where e.containerno >'0' AND e.usecompleted='0'
            </string>
        sb.AppendLine(strsql)

        'Modify by Yuxj20180724装配表打印时，此处不用Bom进行筛选
        If Not para.ContainsKey("StepCheckPointPrint") Then
            If para("HasBom") = "有" Then
                sb.AppendLine(" and e.bigsmallbomid >'0' ")
            Else
                sb.AppendLine(" and e.bigsmallbomid is null ")
            End If
        End If

        If para.ContainsKey("TaskBill") Then
            sb.AppendFormat(" and e.ContainerNo like '%{0}%'", para("TaskBill"))
        End If

        If para.ContainsKey("ContainerName") Then
            sb.AppendFormat(" and e.ContainerName='{0}'", para("ContainerName"))
        End If

        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" and m.factoryid='{0}'", para("FactoryId"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and e.machineno like '%{0}%'", para("MachineStart"))
        End If
        If para.ContainsKey("MfgOrder") Then
            sb.AppendFormat(" and e.mfgordername like '%{0}%'", para("MfgOrder"))
        End If

        'Modify by Yuxj20180622 增加任务单状态筛选
        If para.ContainsKey("Status") Then
            If para("Status") = "未生成" Then
                sb.AppendLine(" and NVL(e.containerid,e.enginecontainerid) is null ") 'Modify by Yuxj20180905 im.containerid=>e.containerid
            Else
                sb.AppendLine(" and NVL(e.containerid,e.enginecontainerid)>'0' ") '只有部件批次才有大小件目录 modify by longb 2023-10-16
            End If
        End If
        sb.AppendLine(" Order by e.plannedfinishdate, e.workflowstepid,e.mfgordername,e.productname,e.containername asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetIssueERPMaterial = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function




    Private Function GetIssueERPMaterialPrint(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetIssueERPMaterialPrint
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT u.attributevalue, e.containerid,e.containername,mfg.mfgordername,mfg.mfgorderid,pb.productname,p.description, 
                 e.containerno,e.containerno togetherflag,                
                 e.workflowstepid,e.workflowid,wfs.workflowstepname,wfs.sequence,                 
                 e.plannedstartdate, e.plannedfinishdate,pf.productfamilyname,
                 wfb.workflowname||':'||wf.workflowrevision workflowname
                FROM enginepartplan e 
                INNER JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
                INNER JOIN (SELECT ep.containerno,MIN(wfs.sequence) wfssequence FROM enginepartplan ep
                    LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                    WHERE ep.usecompleted='0' AND ep.containerno IS NOT NULL 
                    GROUP BY ep.containerno
                    ) EP on ep.containerno=e.containerno and ep.wfssequence=wfs.sequence                 
                 INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid AND mfg.productid != e.productid
                 LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid            
                 LEFT JOIN (select containerno,containerid,count(1) from IssueERPMaterial where ischange='0'
                 group by containerno,containerid ) im ON e.containerid=im.containerid

                 LEFT JOIN product p ON e.productid=p.productid
                 LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                 --Modify by Yuxj20180702 根据工艺获取factoryid
                 LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
                 LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                 LEFT JOIN Factory f ON f.factoryid=wf.factoryid                 
                --Modify by Yuxj20180725 添加机型查询
                 LEFT JOIN workflow wf1 ON wf1.workflowid=wfs.workflowid
                 LEFT JOIN workflowproductfamilylist wff ON wf1.workflowid=wff.workflowid
                 LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid
                where e.containerno is not null  AND u.userattributename='发动机'     
            </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("StepCheckPointPrint") Then
            'Modify by Yuxj20180816 没有生成任务单的不显示
            sb.AppendLine(" AND e.containerId is not null ")
        End If
        If para.ContainsKey("TaskBill") Then
            sb.AppendFormat(" and e.ContainerNo like '%{0}%'", para("TaskBill"))
        End If

        If para.ContainsKey("ContainerName") Then
            sb.AppendFormat(" and e.ContainerName='{0}'", para("ContainerName"))
        End If

        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" and f.factoryid='{0}'", para("FactoryId"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%'", para("MachineStart"))
        End If

        If para.ContainsKey("MfgOrder") Then
            sb.AppendFormat(" and mfg.mfgordername like '%{0}%'", para("MfgOrder"))
        End If
        sb.AppendLine(" Order by wfs.sequence,mfg.mfgordername,pb.productname,e.containername asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetIssueERPMaterialPrint = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    Private Function AutoMatchTaskBill(ByVal para As Dictionary(Of String, String)) As String Implements uLMSIssueERPMaterialIbusiness.AutoMatchTaskBill
        Dim res As String = DatabaseHelper.RunOraProcedure("AUTO_MATCHISSUE_SP", para)
        Return res
    End Function

    Private Function GetBomList(ByVal BomId As String) As System.Data.DataTable Implements uLMSIssueERPMaterialIbusiness.GetBomList
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT bb.bomname, b.bomid,pl.Productid,pb.productname OriginalProductName,pl.Qtyrequired OriginalFirstAssembleQty FROM bom b ")
        sb.AppendLine("LEFT JOIN productmateriallistitem PL ON b.bomid=pl.Bomid ")
        sb.AppendLine("LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid ")
        sb.AppendLine("LEFT JOIN product p ON pl.Productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendFormat("WHERE b.bomid='{0}'", BomId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        If dt Is Nothing Then
            GetBomList = New DataTable
        Else
            GetBomList = dt
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj 根据BomId统计零件需求信息
    ''' Modify by Yuxj20180627 增加根据初检装显示数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CalcBomData(ByVal para As Dictionary(Of String, String)) As System.Data.DataTable Implements uLMSIssueERPMaterialIbusiness.CalcBomData
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT ")
        sb.AppendLine("bb.bomname,pl.Bigsmallpart, b.bomid,pl.Productid,p.description, pb.productname OriginalProductName,pb.productname,s.SPECNO||'-'||s.OPDESC SPECNAME ")
        If para("FirstSecondIssue") = "2" Then
            sb.AppendLine(" ,sum(nvl(pl.Qty2required,pl.Qtyrequired)) OriginalFirstAssembleQty,sum(nvl(pl.Qty2required,pl.Qtyrequired)) FirstAssembleQty ")
        Else
            sb.AppendLine(" ,sum(pl.Qtyrequired) OriginalFirstAssembleQty,sum(pl.Qtyrequired) FirstAssembleQty ") '==>Modify by Yuxj20180601 FirstAssembleQty
        End If

        sb.AppendLine(" FROM bom b ")
        sb.AppendLine("LEFT JOIN productmateriallistitem PL ON b.bomid=pl.Bomid ")
        sb.AppendLine("  LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid ")
        sb.AppendLine("LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid ")
        sb.AppendLine("LEFT JOIN product p ON pl.Productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("left join specbase sb on sb.specbaseid=PL.specbaseid ") 'add by longb 2022-05-23 新增关联工序
        sb.AppendLine("left join spec s on s.specid=nvl(sb.revofrcdid,PL.specid) ") 'add by longb 2022-05-23 新增关联工序
        sb.AppendFormat("WHERE  bt.billtypename='大小件目录' and b.bomid='{0}'", para("BomId"))
        'add by longb 2022-05-23 新增关联工序
        If para.ContainsKey("specid") Then
            If Not String.IsNullOrWhiteSpace(para("specid")) Then
                sb.AppendFormat(" and s.specid= '{0}' ", para("specid"))
            End If
        End If
        sb.AppendLine(" group by bb.bomname,pl.Bigsmallpart, b.bomid,pl.Productid,pb.productname,p.description,s.SPECNO||'-'||s.OPDESC") 'add by longb 2022-07-18---SPECNAME
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        '统计信息，一定能查询到值
        If dt.Rows.Count = 0 Then
            CalcBomData = New DataTable
        Else
            If dt.Rows(0)("bomname").ToString = String.Empty Then
                CalcBomData = New DataTable
            Else
                'Modify by Yuxj20190105 取消整机Bom检查
                CalcBomData = dt
                Exit Function


                'Modify by Yuxj20181109 增加整机Bom判断，不在整机Bom的件号会被取消
                Dim m_PartPlan As New uLMSPartPlanBusiness

                Dim FPName As String = para("EngineProduct")
                Dim dtWholeBom As DataTable = m_PartPlan.GetEngineWholeBom(FPName)
                Dim drsWhole As DataRow()
                'For iCount As Integer = dt.Rows.Count - 1 To 0 Step -1
                '    strTmpProduct = dt.Rows(iCount)("ProductName")
                '    If strTmpProduct = FPName Then
                '        Continue For
                '    End If

                '    drsWhole = dtWholeBom.Select(String.Format("idnrk='{0}'", strTmpProduct))
                '    If drsWhole.Length = 0 Then
                '        dt.Rows.RemoveAt(iCount)
                '    End If
                'Next
                'Modify by Yuxj20181124通过筛选，减少循环次数
                For Each drW As DataRow In dt.DefaultView.ToTable(True, "productname").Rows
                    If drW("productname") = FPName Then
                        Continue For
                    End If

                    drsWhole = dtWholeBom.Select(String.Format(String.Format("idnrk='{0}'", drW("productname"))))
                    If drsWhole.Length = 0 Then
                        Dim drsNotNull As DataRow() = dt.Select(String.Format("productname='{0}'", drW("productname")))
                        For Each dr As DataRow In drsNotNull
                            dr.Delete()
                        Next
                    End If
                Next
                dt.AcceptChanges()
                CalcBomData = dt
            End If
        End If
    End Function

    ''' <summary>
    ''' Add bu Yuxj20180510 根据工艺统计零件需求信息
    ''' Modify by Yuxj20181018 
    ''' decode(pl.Bigsmallpart,'大零件',0,'小零件',1，pl.Bigsmallpart)->decode(pl.Bigsmallpart,'大零件','0','小零件','1'，pl.Bigsmallpart)
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CalcBomDatabyWorkflowid(ByVal para As Dictionary(Of String, String)) As System.Data.DataTable Implements uLMSIssueERPMaterialIbusiness.CalcBomDatabyWorkflowid
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT pl.Productid,pb.productname OriginalProductName,decode(pl.Bigsmallpart,'大零件','0','小零件','1'，pl.Bigsmallpart) parttype,sum(pl.Qtyrequired) OriginalFirstAssembleQty ")
        'Modify by Yuxj20180625 增加记录必换件,添加检装件记录
        sb.AppendLine(",decode(pl.notes,'必换件'，'1','0') notes,sum(pl.Qty2required) OriginalCheckAssembleQty,s.SPECID ")
        sb.AppendLine(" FROM bom b  ")
        sb.AppendLine("inner JOIN productmateriallistitem PL ON b.bomid=pl.Bomid ")
        sb.AppendLine("LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid ")
        sb.AppendLine("LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid ")
        sb.AppendLine("LEFT JOIN product p ON pl.Productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("left join specbase sb on sb.specbaseid=PL.specbaseid ") '2022-05-19 add by longb
        sb.AppendLine("left join spec s on s.specid=nvl(sb.revofrcdid,PL.specid) ") '2022-05-19 add by longb
        sb.AppendFormat("WHERE b.workflowid='{0}'", para("WorkFlowId"))
        sb.AppendLine(" and bt.billtypename='大小件目录' ")
        sb.AppendLine(" group by pl.Productid,pb.productname,decode(pl.Bigsmallpart,'大零件','0','小零件','1'，pl.Bigsmallpart),pl.notes,s.SPECID ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        'Modify by Yuxj20190105 取消整机Bom检查
        If dt.Rows.Count = 0 Then
            CalcBomDatabyWorkflowid = New DataTable
        Else
            CalcBomDatabyWorkflowid = dt
        End If
        Exit Function


        'Modify by Yuxj20180710,获取发动机件号对应Bom 列表，工艺对应Bom对应的件号需在此存在
        Dim m_PartPlan As New uLMSPartPlanBusiness

        Dim FPName As String = para("EngineProduct")
        Dim dtWholeBom As DataTable = m_PartPlan.GetEngineWholeBom(FPName)
        Dim drsWhole As DataRow()
        'For iCount As Integer = dt.Rows.Count - 1 To 0 Step -1
        '    strTmpProduct = dt.Rows(iCount)("ProductName")
        '    If strTmpProduct = FPName Then
        '        Continue For
        '    End If

        '    drsWhole = dtWholeBom.Select(String.Format("idnrk='{0}'", strTmpProduct))
        '    If drsWhole.Length = 0 Then
        '        dt.Rows.RemoveAt(iCount)
        '    End If
        'Next

        'Modify by Yuxj20181124通过筛选，减少循环次数
        For Each drW As DataRow In dt.DefaultView.ToTable(True, "OriginalProductName").Rows
            If drW("OriginalProductName") = FPName Then
                Continue For
            End If

            drsWhole = dtWholeBom.Select(String.Format(String.Format("idnrk='{0}'", drW("OriginalProductName"))))
            If drsWhole.Length = 0 Then
                Dim drsNotNull As DataRow() = dt.Select(String.Format("OriginalProductName='{0}'", drW("OriginalProductName")))
                For Each dr As DataRow In drsNotNull
                    dr.Delete()
                Next
            End If
        Next
        dt.AcceptChanges()

        '统计信息，一定能查询到值
        If dt.Rows.Count = 0 Then
            CalcBomDatabyWorkflowid = New DataTable
        Else
            CalcBomDatabyWorkflowid = dt
        End If
    End Function

    Private Function GetPartMenuinfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetPartMenuinfo
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT ")
        If para("FirstSecondIssue") = "2" Then
            sb.AppendLine(" pm.originalcheckassembleqty originalfirstassembleqty,pm.checkassembleqty firstassembleqty, ")
        Else
            sb.AppendLine(" pm.originalfirstassembleqty,pm.firstassembleqty, ")
        End If
        sb.AppendLine(" pm.partmenuinfoid,p.description, pm.originalproductid,pm.productid,pb.productname,pb1.productname OriginalProductName, ")
        sb.AppendLine(" pm.changereason,pm.parttype,p.description pdesc,pm.firstassembleqty qtyrequired,  ")
        sb.AppendLine(" decode(pm.effectiveness,'0','无效','1','长期','2','单台'，effectiveness) effectiveness,pm.specid")
        sb.AppendLine(",containerNo ")
        sb.AppendLine(" FROM partmenuinfo pm ")
        sb.AppendLine(" LEFT JOIN product p ON pm.productid=p.productid")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid")
        sb.AppendLine(" LEFT JOIN product p1 ON pm.originalproductid=p1.productid")
        sb.AppendLine(" LEFT JOIN productbase pb1 ON p1.productbaseid=pb1.productbaseid")
        'Modify by Yuxj20180509 资料存储方式更改，不存ContainerId入partmenuinfo
        'sb.AppendFormat("WHERE pm.containerNo='{0}' and pm.containerid='{1}'", para("ContainerNo"), para("ContainerId"))
        sb.AppendFormat("WHERE pm.containerNo='{0}' ", para("ContainerNo"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        If dt.Rows.Count = 0 Then
            GetPartMenuinfo = New DataTable
        Else
            GetPartMenuinfo = dt
        End If
    End Function

    Private Function InsertPartMenu(ByVal paraMenu As Dictionary(Of String, String)) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertPartMenu
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO partmenuinfo ")
        sb.AppendLine("(checkassembleqty, ContainerNo, createdate, employeeid, note, partmenuinfoid, ")
        sb.AppendLine("parttype, productid, changereason, effectiveness, originalcheckassembleqty, originalfirstassembleqty, originalproductid, firstassembleqty) ")
        sb.AppendLine("values ( ")
        sb.AppendFormat("'0','{0}',sysdate,'{1}',null,'{2}','0','{3}',null,'1','0','{4}','{5}','{6}' ",
                        paraMenu("ContainerNo"), paraMenu("UserId"), paraMenu("Guid"), paraMenu("ProductId"), paraMenu("Qty"), paraMenu("ProductId"), paraMenu("Qty"))
        sb.AppendLine(" ) ")
        DatabaseHelper.ExecuteSql(sb.ToString)
        InsertPartMenu = True
    End Function

    ''' <summary>
    ''' Modify by Yuxj20180625增加备注记录必换件
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertPartMenuTable(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertPartMenuTable
        InsertPartMenuTable = False
        Dim objPara As New Object()
        objPara = FillInsertPartMenu(entity)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(objPara(0), objPara(1))
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function
    ''' <summary>
    ''' 填充零件目录插入语句
    ''' create by Yangsj 2020-7-22
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function FillInsertPartMenu(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Object() Implements uLMSIssueERPMaterialIbusiness.FillInsertPartMenu
        Dim strsql As String = <string>
                INSERT INTO partmenuinfo
                   (checkassembleqty,containerno, createdate, employeeid, firstassembleqty, note, partmenuinfoid, parttype, productid, 
                    changereason, effectiveness, originalcheckassembleqty, originalfirstassembleqty, originalproductid,specid)
                VALUES
                    (:checkassembleqty, :containerno, sysdate, :employeeid, :firstassembleqty, :note, :partmenuinfoid, :parttype,
                     :productid,   :changereason, :effectiveness, :originalcheckassembleqty, :originalfirstassembleqty, :originalproductid,:specid)
            </string>

        Dim insertpar(13) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":checkassembleqty", entity.CheckAssembleQty, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":containerno", entity.ContainerNo, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeId, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":firstassembleqty", entity.FirstAssembleQty, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":partmenuinfoid", entity.PartMenuInfoID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":parttype", entity.PartType, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":productid", entity.ProductID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":changereason", entity.Reason, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":effectiveness", entity.Effectiveness, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":originalcheckassembleqty", entity.OriginalCheckAssembleQty, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":originalfirstassembleqty", entity.OriginalFirstAssembleQty, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":originalproductid", entity.OriginalProductID, OracleType.VarChar)
        'Modify by Yuxj20180625 增加备注填入必换件
        insertpar(12) = DatabaseHelper.NewParameter(":Note", entity.Note, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":specid", entity.SpecId, OracleType.VarChar)
        Return New Object() {strsql, insertpar}
    End Function

    ''' <summary>
    ''' Modify by Yuxj20180626 更新初检装数量
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UpdatePartMenuInfo(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.UpdatePartMenuInfo
        Dim sb As New StringBuilder
        'Modify by Yuxj20180626 根据情况更新初检装数量
        sb.AppendLine(" UPDATE partmenuinfo pm SET pm.productid=:productid, ")
        If entity.CheckAssembleQty = "0" Then
            '更新检装数量
            sb.AppendLine(" pm.checkassembleqty=:firstassembleqty, ")
        Else
            '更新初装数量
            sb.AppendLine(" pm.firstassembleqty=:firstassembleqty, ")
        End If
        sb.AppendLine(" pm.changereason=:changereason,pm.effectiveness=:effectiveness")
        sb.AppendLine(" WHERE pm.partmenuinfoid=:partmenuinfoid  ")
        Dim insertpar(4) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":productid", entity.ProductID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":changereason", entity.Reason, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":effectiveness", entity.Effectiveness, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":firstassembleqty", entity.FirstAssembleQty, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":partmenuinfoid", entity.PartMenuInfoID, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(sb.ToString, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 删除零件目录信息
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DeletePartMenuInfo(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.DeletePartMenuInfo
        Dim objpara As New Object()
        objpara = FillDeletePartMenuInfo(entity)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(objpara(0), objpara(1))
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function
    ''' <summary>
    ''' 填充删除零件目录语句
    ''' create by Yangsj 2020-7-22
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function FillDeletePartMenuInfo(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Object() Implements uLMSIssueERPMaterialIbusiness.FillDeletePartMenuInfo
        Dim strsql As String = <string>delete from partmenuinfo pm 
                WHERE pm.partmenuinfoid=:partmenuinfoid  </string>
        Dim para(0) As OracleParameter
        para(0) = DatabaseHelper.NewParameter(":partmenuinfoid", entity.PartMenuInfoID, OracleType.VarChar)
        Return New Object() {strsql, para}
    End Function

    Private Function GetCollectStockQty(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSIssueERPMaterialIbusiness.GetCollectStockQty
        Dim ds As New DataSet
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT CM.COLLECTERPMATERIALID,CM.ISAUTOPLAN,CM.ISNONCONFORMING,
                CM.MFGORDERNAME,CM.PRODUCTBATCHNO,CM.PRODUCTNAME,IM.ISSUEQTY,CM.QTY,
                cm.qty-nvl(im.ISSUEQTY,0) stockqty, m.plannedstartdate
                  FROM COLLECTERPMATERIAL CM
                LEFT JOIN MFGORDER M ON CM.MFGORDERNAME=M.MFGORDERNAME
                  LEFT JOIN (SELECT IML.COLLECTERPMATERIALID, SUM(IML.ISSUEQTY) ISSUEQTY
                               FROM ISSUEERPMATERIAL IML where IML.ischange='0'
                              GROUP BY IML.COLLECTERPMATERIALID  ) IM
                    ON CM.COLLECTERPMATERIALID = IM.COLLECTERPMATERIALID
            </string>)
        sb.AppendLine("where  (cm.qty-nvl(im.ISSUEQTY,0))>0 ")
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and CM.MFGORDERNAME='{0}' ", para("MfgOrderName"))
        End If
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and CM.PRODUCTNAME='{0}' ", para("ProductName"))
        End If
        'Modify by Yuxj20180727 按照时间降序排列，空的排后边，对日期格式，降序排列默认空的在前
        sb.AppendLine("Order by   nvl(m.plannedstartdate,to_date('1000/01/01','YYYY/MM/DD')),CM.MFGORDERNAME desc, CM.PRODUCTBATCHNO asc, CM.PRODUCTNAME asc")

        Dim dtStock As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dtStock.TableName = "Stock"
        ds.Tables.Add(dtStock)
        If Not para.ContainsKey("ContainerId") OrElse para.ContainsKey("OnlyStock") Then
            Return ds
            Exit Function
        End If

        sb = New StringBuilder
        sb.AppendLine("SELECT im.IssueERPMaterialID,cm.productname,cm.productbatchno,")
        'Modify by Yuxj20180709 添加订单列，为判断跨订单配套使用
        sb.AppendLine("cm.mfgorderName, ")
        If para("FirstSecondIssue") = "2" Then
            sb.AppendLine(" pm.checkassembleqty  firstassembleqty ,  ")
        Else
            sb.AppendLine(" pm.firstassembleqty ,  ")
        End If
        'Modify by Yuxj20180627,添加对报废信息的过滤，如果批次全部报废，则不显示
        sb.AppendLine(" im.issueqty-nvl(ims.lossqty,0) issueqty, ")
        sb.AppendLine("im.note FROM IssueERPMaterial im ")
        sb.AppendLine(" LEFT JOIN (SELECT ims.issueerpmaterialid,SUM(nvl(ims.lossqty,0)) lossqty ")
        sb.AppendLine(" FROM issueerpmaterialscrapinfo ims GROUP BY ims.issueerpmaterialid ) ims ")
        sb.AppendLine(" ON im.issueerpmaterialid=ims.issueerpmaterialid ")
        sb.AppendLine("LEFT JOIN COLLECTERPMATERIAL CM ON im.collecterpmaterialid=cm.collecterpmaterialid ")
        sb.AppendLine("LEFT JOIN PartMenuInfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("where  pm.firstassembleqty>0 and im.ischange='0'")
        sb.AppendLine(" and im.issueqty-nvl(ims.lossqty,0)>0 ")
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and CM.MFGORDERNAME='{0}' ", para("MfgOrderName"))
        End If
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and CM.PRODUCTNAME='{0}' ", para("ProductName"))
        End If

        If para.ContainsKey("ContainerId") Then
            sb.AppendFormat(" and IM.ContainerId='{0}' ", para("ContainerId"))
        End If

        Dim dtIssue As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dtIssue.TableName = "Issue"
        ds.Tables.Add(dtIssue)
        Return ds
    End Function

    Private Function DeleteIssueERPMaterial(ByVal strid As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.DeleteIssueERPMaterial
        Dim strsql As String = String.Format("delete from IssueERPMaterial where IssueERPMaterialid='{0}' ", strid)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function InsertIssueErpMaterial(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertIssueErpMaterial
        InsertIssueErpMaterial = False
        Dim objPara As Object()
        objPara = FillInsertIssueErpMaterial(entity)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(objPara(0), objPara(1))
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function FillInsertIssueErpMaterial(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Object() Implements uLMSIssueERPMaterialIbusiness.FillInsertIssueErpMaterial
        Dim strsql As String =
             <string>
                INSERT INTO issueerpmaterial
                    (bommateriallistitemid, collecterpmaterialid, containerNo,containerid, createdate, employeeid, 
                     factoryid, issueqty, productchildno, workflowstepid, note, issueerpmaterialid, 
                     toissueerpmaterialid, assembletimes, partmenuinfoid,ischange)
                VALUES
                    (:bommateriallistitemid, :collecterpmaterialid, :containerNo,:containerid, sysdate, :employeeid, 
                     :factoryid, :issueqty, :productchildno, :workflowstepid, :note, :issueerpmaterialid, 
                     :toissueerpmaterialid, :assembletimes, :partmenuinfoid,'0')
            </string>
        Dim insertpar(13) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":BommateriallistitemID", entity.BommateriallistitemID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":CollectERPMaterialID", entity.CollectERPMaterialID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":ContainerNo", entity.ContainerNo, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":EmployeeId", entity.EmployeeId, OracleType.VarChar)

        insertpar(4) = DatabaseHelper.NewParameter(":FactoryID", entity.FactoryID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":IssueQty", entity.IssueQty, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":WorkflowStepID", entity.WorkflowStepID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":Note", entity.Note, OracleType.VarChar)

        insertpar(8) = DatabaseHelper.NewParameter(":IssueERPMaterialID", entity.IssueERPMaterialID, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":toissueerpmaterialid", entity.ToIssueERPMaterialID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":AssembleTimes", entity.AssembleTimes, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":PartMenuInfoID", entity.PartMenuInfoID, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":productchildno", entity.ProductChildNO, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)

        Return New Object() {strsql, insertpar}

    End Function

    Function GetPartMenuInfoFirstQty(ByVal strid As String) As String Implements uLMSIssueERPMaterialIbusiness.GetPartMenuInfoFirstQty
        Dim strsql As String = String.Format("SELECT firstassembleqty FROM partmenuinfo WHERE partmenuinfoid='{0}'", strid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count = 0 Then
            Return "0"
        Else
            Return dt.Rows(0)("firstassembleqty").ToString
        End If
    End Function

    ''' <summary>
    ''' Modify by Yuxj20180210
    ''' 获取大小件配件明细
    ''' Modify by Yuxj2018年5月23日 
    ''' 一个任务单在发动机的多个阶段存在，如果本阶段没有集件，显示前面最后阶段有集件的信息
    ''' Modify by Yuxj20180604增加对报废品的统计
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetIssueMenuInfoDS(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSIssueERPMaterialIbusiness.GetIssueMenuInfoDS
        Dim ds As New DataSet
        'Modify by Yuxj20180626,判断初检装，初装显示初装数量，检装显示检装数量
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerId"))
        Dim strsqlPrefix As String = " select pm.firstassembleqty, "
        If strFirstSecondIssue = "2" Then
            strsqlPrefix = "select pm.checkassembleqty firstassembleqty," 'modify by longb 2023-01-10
            'strsqlPrefix = " select decode(pm.checkassembleqty,null,pm.firstassembleqty,0,pm.firstassembleqty,pm.checkassembleqty) firstassembleqty, "
        End If

        Dim strsql As String =
            <string>
                --SELECT im.containerno,im.containerid, cm.productname,p.description,pm.firstassembleqty,im.issueqty
                 e1.fullname ASSEMBLEEMPLOYEE, e2.fullname ACCEPTEMPLOYEE,ACCEPTDATE,IM.STATUS,
                 pm.partmenuinfoid,im.containerno,im.containerid, cm.productname,p.description,im.issueqty
                ,isc.lossqty,im.issueqty-nvl(isc.lossqty,0) spanQty,cm.productbatchno,pm.parttype,im.issueerpmaterialId,con.CONFIRMQTY,s.SPECNO||'-'||s.OPDESC SPECNAME 
                FROM issueerpmaterial im                
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid               
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                LEFT JOIN product p ON p.productid=pm.productid
                LEFT JOIN employee e1 ON e1.employeeid=im.ASSEMBLEEMPLOYEEID
                LEFT JOIN employee e2 ON e2.employeeid=im.ACCEPTEMPLOYEEID
                LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ISSUEERPMATERIALID FROM issueerpmaterialconfirmation where mfgorderid='{0}' GROUP BY ISSUEERPMATERIALID) con ON con.ISSUEERPMATERIALID=im.ISSUEERPMATERIALID
                LEFT JOIN (SELECT isc.issueerpmaterialid,sum(isc.lossqty) lossqty FROM issueerpmaterialscrapinfo isc group by isc.issueerpmaterialid) isc
                        on isc.issueerpmaterialid=im.issueerpmaterialid
                LEFT JOIN Spec s ON s.SPECID =pm.SPECID--add by longb 2022-07-18
            </string>
        strsql = strsqlPrefix + String.Format(strsql, para("mfgorderId")) + String.Format("where im.containerNo='{0}' and im.ischange='0' and pm.parttype='{1}' and im.containerid='{2}' ", para("ContainerNo"), para("PartType"), para("ContainerId"))
        'Modify by Yuxj20180628
        strsql += " and im.issueqty-nvl(isc.lossqty,0)>0 "
        'add by longb 2022-05-23
        If para.ContainsKey("specid") Then
            If Not String.IsNullOrWhiteSpace(para("specid")) Then
                strsql += String.Format(" and pm.specid= '{0}' ", para("specid"))
            End If
        End If
        If para.ContainsKey("mfgorderId") Then
            If Not String.IsNullOrWhiteSpace(para("mfgorderId")) Then
                strsql += String.Format(" and ep.mfgorderId= '{0}' ", para("mfgorderId"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件
            strsql += " AND nvl(pm.checkassembleqty,0)>0"
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)

        If dt.Rows.Count = 0 Then
            strsql = <string>
                         e1.fullname ASSEMBLEEMPLOYEE, e2.fullname ACCEPTEMPLOYEE,ACCEPTDATE,IM.STATUS,pm.partmenuinfoid,
                        im.containerno,im.containerid,p.description, cm.productname,
                            im.issueqty,isc.lossqty,im.issueqty-nvl(isc.lossqty,0) spanQty,cm.productbatchno,pm.parttype,im.issueerpmaterialId,con.CONFIRMQTY,s.SPECNO||'-'||s.OPDESC SPECNAME  
                        FROM issueerpmaterial im                
                        LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                        LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                        LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid
                        LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                        LEFT JOIN product p ON p.productid=pm.productid
                        LEFT JOIN employee e1 ON e1.employeeid=im.ASSEMBLEEMPLOYEEID
                        LEFT JOIN employee e2 ON e2.employeeid=im.ACCEPTEMPLOYEEID
                        LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ISSUEERPMATERIALID FROM issueerpmaterialconfirmation where mfgorderid='{0}' GROUP BY ISSUEERPMATERIALID) con ON con.ISSUEERPMATERIALID=im.ISSUEERPMATERIALID
                        LEFT JOIN (SELECT isc.issueerpmaterialid,sum(isc.lossqty) lossqty FROM issueerpmaterialscrapinfo isc group by isc.issueerpmaterialid) isc
                                on isc.issueerpmaterialid=im.issueerpmaterialid
                        LEFT JOIN Spec s ON s.SPECID =pm.SPECID--add by longb 2022-07-18
                        LEFT JOIN workflowstep wfs1 ON wfs1.specbaseid=s.specbaseid OR wfs1.specid=pm.specid
                     </string>
            strsql = strsqlPrefix + String.Format(strsql, para("mfgorderId")) + String.Format("where im.containerNo='{0}' and im.ischange='0' and pm.parttype='{1}' ", para("ContainerNo"), para("PartType"))
            strsql = strsql + String.Format(" AND wfs1.workflowid='{0}'", para("WorkflowId"))
            'add by longb 2022-05-23
            If para.ContainsKey("specid") Then
                If Not String.IsNullOrWhiteSpace(para("specid")) Then
                    strsql += String.Format(" and pm.specid= '{0}' ", para("specid"))
                End If
            End If
            If para.ContainsKey("mfgorderId") Then
                If Not String.IsNullOrWhiteSpace(para("mfgorderId")) Then
                    strsql += String.Format(" and ep.mfgorderId= '{0}' ", para("mfgorderId"))
                End If
            End If
            If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件
                strsql += " AND nvl(pm.checkassembleqty,0)>0"
            End If
            strsql += " ORDER BY wfs.sequence ASC"
            dt = DatabaseHelper.Query_ReturnDatTable(strsql)
            If dt.Rows.Count > 0 Then
                Dim containerId As String = dt.Rows(dt.Rows.Count - 1)("containerid").ToString
                Dim dv As DataView = dt.DefaultView
                dv.RowFilter = String.Format("containerid='{0}'", containerId)
                dt = dv.ToTable
            End If
        End If

        Dim dtIssueDetail As DataTable = dt.DefaultView.ToTable(True, "issueerpmaterialId", "productname", "productbatchno", "firstassembleqty", "issueqty", "parttype")
        Dim dtIssueSum As DataTable = dt.Copy.DefaultView.ToTable(True, "containerno", "productname", "description", "firstassembleqty", "productbatchno", "parttype", "issueerpmaterialId", "ASSEMBLEEMPLOYEE", "ACCEPTEMPLOYEE", "ACCEPTDATE", "STATUS", "SPECNAME") 'modify by longb 2022-07-18 SPECNAME
        dtIssueSum.Columns.Add("SumQty", Type.GetType("System.Int32"))
        For Each dr As DataRow In dtIssueSum.Rows  'modify by longb 2022-05-23 spanQty-->CONFIRMQTY
            dr("SumQty") = dt.Compute("sum(CONFIRMQTY)", String.Format("productname='{0}' and parttype='{1}' and productbatchno='{2}' and SPECNAME='{3}'", dr("productname").ToString, dr("parttype").ToString, dr("productbatchno").ToString, dr("SPECNAME").ToString))
        Next
        dtIssueSum.AcceptChanges()
        'Modify by Yuxj20180720增加排序
        Dim dvSum As DataView = dtIssueSum.DefaultView
        dvSum.Sort = "specname,productname,productbatchno asc"
        dtIssueSum = dvSum.ToTable

        dtIssueSum.TableName = "dtIssueSum"
        ds.Tables.Add(dtIssueSum)
        dtIssueDetail.TableName = "dtIssueDetail"
        ds.Tables.Add(dtIssueDetail)

        'ds.Relations.Add(dtIssueSum.Columns("productname"), dtIssueDetail.Columns("productname"))
        Return ds
    End Function

    ''' <summary>
    ''' Modify by Yuxj20190428
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <param name="ContainerNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetMfgContainerDT(ByVal mfgid As String, ByVal ContainerNo As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetMfgContainerDT
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT NVL(e.containerid,e.enginecontainerid) containerid,e.containername, c.containername cname,e.containerno togetherflag, ")
        sb.AppendLine("e.plannedstartdate,e.plannedfinishdate,e.productid,e.workflowid,m.mfgorderid,m.mfgordername, ")
        sb.AppendLine("p.productrevision,pb.productname,wf.workflowrevision,wfb.workflowname ")
        sb.AppendLine("FROM enginepartplan e  ")
        sb.AppendLine("LEFT JOIN mfgorder m ON e.mfgorderid=m.mfgorderid ")
        sb.AppendLine("LEFT JOIN container c ON e.containername=c.containername and c.status='1'")
        sb.AppendLine("LEFT JOIN product p ON e.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN workflow wf ON e.workflowid=wf.workflowid ")
        sb.AppendLine("LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid ")

        sb.AppendFormat("where m.mfgorderId='{0}' and e.containername is not null ", mfgid)
        'Modify by Yuxj20180917，只生成勾选的任务单记录（有大小件目录的部分），没有Bom/有Bom但为消耗件 的全部生成
        sb.AppendLine(" and ( wf.workflowid not in ( ")
        sb.AppendLine(" SELECT distinct wf.workflowid FROM workflow wf INNER JOIN bom b ON wf.workflowid=b.workflowid ")
        sb.AppendLine(" LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid WHERE bt.billtypename='大小件目录' )")
        sb.AppendFormat(" or e.containerno ='{0}' )", ContainerNo)

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim dv As DataView = dt.DefaultView

        'Modify by Yuxj20190428
        'dv.RowFilter = "cname is null"
        dv.RowFilter = "containerid is null"
        dt = dv.ToTable
        Return dt
    End Function

    ''' <summary>
    ''' 根据条件回填EnginePartPlan，发动机件号填enginecontainerid 部件件号填containerid
    ''' Modify by Yuxj20180628
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <param name="cname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UpdateEnginePartPlanID(ByVal cid As String, ByVal cname As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.UpdateEnginePartPlanID
        UpdateEnginePartPlanID = False
        '判断如果是大阶段批次更新enginecontainerid，否则更新到ContainerId
        Dim sb As New StringBuilder

        'Modify by Yuxj20190111 条件ep.productid=m.productid有异常，故修改为NVL(pb.revofrcdid,m.productid)=p.productid
        'sb.AppendLine("SELECT ep.* FROM enginepartplan ep  ")
        ''Modify by Yuxj20180628 ep.productid=m.mfgorderid=>ep.productid=m.productid
        'sb.AppendLine("INNER JOIN mfgorder m ON ep.mfgorderid=m.mfgorderid AND ep.productid=m.productid ")
        sb.AppendLine("SELECT ep.* FROM mfgorder m ")
        sb.AppendLine("LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid ")
        sb.AppendLine("LEFT JOIN enginepartplan ep ON m.mfgorderid=ep.mfgorderid   AND ep.productid=p.productid ")
        sb.AppendFormat("WHERE ep.containername='{0}' ", cname)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim strsql As String = String.Empty
        If dt.Rows.Count > 0 Then
            strsql = String.Format("update EnginePartPlan e set e.enginecontainerid='{0}' where e.containername='{1}' and e.enginecontainerid is null ", cid, cname)
        Else
            strsql = String.Format("update EnginePartPlan e set e.containerid='{0}' where e.containername='{1}' and e.containerid is null ", cid, cname)
        End If

        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function
    ''' <summary>
    '''  Create by Yangsj 20200722
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <param name="cname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function FillUpdateEnginePartPlanID(ByVal cid As String, ByVal cname As String, ByVal isEnginePart As Boolean) As Object() Implements uLMSIssueERPMaterialIbusiness.FillUpdateEnginePartPlanID
        Dim strsql As String = String.Empty
        If isEnginePart Then
            strsql = String.Format("update EnginePartPlan e set e.enginecontainerid='{0}' where e.containername='{1}' and e.enginecontainerid is null ", cid, cname)
        Else
            strsql = String.Format("update EnginePartPlan e set e.containerid='{0}' where e.containername='{1}' and e.containerid is null ", cid, cname)
        End If
        Return New Object() {strsql, Nothing}
    End Function

    ''' <summary>
    ''' Add by Yuxj20180608如果任务单当前阶段没有配套，获取上一个阶段的配套资料
    ''' Add by Yuxj20180612增加对报废品的处理
    ''' </summary>
    ''' <param name="ContainerNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetLastIssueContainerDT(ByVal ContainerNo As String, ByRef Cid As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetLastIssueContainerDT
        Dim strsql = String.Format("SELECT MAX(im.createdate) createdate,im.containerid FROM issueerpmaterial im ")
        'Add by Yuxj20180612增加对报废品的处理
        strsql += " LEFT JOIN issueerpmaterialscrapinfo ims  ON im.issueerpmaterialid=ims.issueerpmaterialid"
        strsql += String.Format(" where im.containerNo='{0}' and im.ischange='0' and  nvl(im.issueqty,0)>nvl(ims.lossqty,0) ", ContainerNo)
        strsql += " GROUP BY im.containerid ORDER BY createdate DESC "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Dim strCid As String = String.Empty
        If dt.Rows.Count > 0 Then
            strCid = dt.Rows(0)("ContainerId").ToString
        Else
            strCid = String.Empty
        End If
        Cid = strCid
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180612 根据批次ID查询配套数据
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIssueErpMaterialbyCid(ByVal cid As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetIssueErpMaterialbyCid
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT im.collecterpmaterialid,im.containerid,im.issueqty-NVL(ims.lossqty,0) issueqty ,im.employeeid, ")
            .AppendLine("im.factoryid,im.partmenuinfoid,im.containerno,DECODE(pm.note,'必换件','1','0') note ")
            .AppendLine(" FROM issueerpmaterial im ")
            'Modify by Yuxj20180626增加必换件属性显示
            .AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
            .AppendLine(" LEFT JOIN issueerpmaterialscrapinfo ims ON im.issueerpmaterialid=ims.issueerpmaterialid ")
            .AppendFormat("WHERE im.containerid='{0}' and im.ischange='0' and im.issueqty-NVL(ims.lossqty,0)>0 ", cid)
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20190627 根据任务单查询配套数据
    ''' </summary>
    ''' <param name="cno"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIssueErpMaterialbyCno(ByVal cno As String, Optional ByVal mfgorderName As String = "") As DataTable Implements uLMSIssueERPMaterialIbusiness.GetIssueErpMaterialbyCno
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT im.collecterpmaterialid,im.containerid,im.issueqty-NVL(ims.lossqty,0) issueqty ,im.employeeid, ")
            .AppendLine("im.factoryid,im.partmenuinfoid,im.containerno,DECODE(pm.note,'必换件','1','0') note ")
            .AppendLine(" FROM issueerpmaterial im ")
            .AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
            .AppendLine(" LEFT JOIN issueerpmaterialscrapinfo ims ON im.issueerpmaterialid=ims.issueerpmaterialid ")
            .AppendLine(" LEFT JOIN enginepartplan e on e.containerid =im.containerid")
            .AppendFormat("WHERE im.containerno='{0}' and im.ischange='0' and im.issueqty-NVL(ims.lossqty,0)>0", cno)
        End With
        If Not String.IsNullOrWhiteSpace(mfgorderName) Then
            sb.AppendFormat(" and e.mfgordername ='{0}'", mfgorderName)
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180710
    ''' 获取发动机件号
    ''' </summary>
    ''' <param name="MfgOrder"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineProduct(ByVal MfgOrder As String) As String Implements uLMSIssueERPMaterialIbusiness.GetEngineProduct
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT pb.productname FROM mfgorder m ")
            .AppendLine("LEFT JOIN product p ON m.productid=p.productid ")
            .AppendLine("LEFT JOIN productbase pb ON pb.productbaseid=NVL(p.productbaseid,m.productbaseid) ")
            .AppendFormat(" where m.mfgorderName='{0}' ", MfgOrder)
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return String.Empty
        Else
            Return dt.Rows(0)("ProductName")
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180724
    ''' 获取任务单号对应的大阶段和部件工艺
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineStepAndWorkflowInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetEngineStepAndWorkflowInfo
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT ep.containerno,ep.containerid,wfs.workflowstepid, wfs.sequence,wfs.workflowstepname,wfb.workflowname,
                wf.description wfdesc ,wf.workflowid,wf.phasenode,p.description productdesc FROM enginepartplan ep 
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid
                LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                LEFT JOIN product p ON ep.productid=p.productid
            </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" where  ContainerNo='{0}'", para("ContainerNo"))
        End If
        'Modify b Yuxj20180903
        If para.ContainsKey("MfgOrderId") Then
            sb.AppendFormat(" and  MfgOrderId='{0}'", para("MfgOrderId"))
        End If

        'Modify b Yuxj20181008
        If para.ContainsKey("WorkFlowId") Then
            sb.AppendFormat(" and  wf.WorkFlowId='{0}'", para("WorkFlowId"))
        End If
        sb.AppendLine(" order by wfs.sequence,wf.workflowid asc ")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Add by Yuxj20180725
    ''' 获取检测项对应的录入信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepCheckPointData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetStepCheckPointData
        '获取工艺对应的检测项及标准值，检验实测值，工人，检验员名称
        '试验工艺有工序没有工步，装配工艺有工步工序
        '试车工艺没有工步
        Dim hasStepInfo As Boolean = True
        If (para("PhaseNode").IndexOf("试验") = -1) Or (para("PhaseNode").IndexOf("试车") = -1) Then
            '没有工步
            hasStepInfo = False
        End If
        'Modify by Yuxj20180726判断初检装
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerId"))

        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT sc.name,sc.drawingrequire checkpointnote, ")
            'Modify by Yuxj20180802 修改检验人（取合格人，如有检验录入者，取检验人）
            '，检验实测值逻辑（如有实测值显示，如无且点了合格，显示为合格）
            'Modify by Yuxj20180820 getempfullname-> GETOnlyFULLNAME
            .AppendLine(" DECODE(cck.containerid,NULL,NVL(scr.checkpointresultnote,scr.workcheckpointresultnote), ")
            .AppendLine(" NVL(scr.checkpointresultnote,NVL(scr.workcheckpointresultnote,'合格'))) checkpointresultnote, ")
            .AppendLine(" DECODE(cck.checkerid,NULL,GETOnlyFULLNAME(scr.checkerid),GETOnlyFULLNAME(cck.checkerid)) checker, ")
            .AppendLine("GETOnlyFULLNAME(scr.workerid) worker ")
            .AppendLine("FROM stepcheckpoint sc ")
            .AppendLine("LEFT JOIN stepcheckpointresult scr ON scr.stepcheckpointid=sc.stepcheckpointid ")

            .AppendFormat("AND scr.containerid='{0}' ", para("ContainerId"))
            If hasStepInfo Then
                .AppendLine("LEFT JOIN stepinfo SI ON sc.stepinfoid=si.Stepinfoid  ")
                .AppendLine("LEFT JOIN workflowstep wfs ON si.workflowstepid=wfs.workflowstepid ")
            Else
                .AppendLine("LEFT JOIN workflowstep wfs ON sc.workflowstepid=wfs.workflowstepid ")
            End If

            'Modify by Yuxj20180823
            '.AppendLine(" LEFT JOIN containercheckinfo cck ON cck.containerid=scr.containerid ")
            .AppendFormat(" LEFT JOIN containercheckinfo cck ON cck.containerid ='{0}' ", para("ContainerId"))
            'Modify by Yuxj20180802
            If hasStepInfo Then
                .AppendLine(" and cck.stepinfoid=sc.stepinfoid  ")
            Else
                .AppendLine(" and cck.workflowstepid=sc.workflowstepid ")
            End If
            .AppendFormat(" where wfs.workflowid='{0}' ", para("WorkflowId"))
            If strFirstSecondIssue = "2" Then
                .AppendLine(" And sc.assemblytype='检装' ")
            Else
                .AppendLine(" And sc.assemblytype='初装' ")
            End If

            .AppendLine(" ORDER BY wfs.sequence ")
            If hasStepInfo Then
                .AppendLine(" ,si.Stepinfoid asc ")
            End If
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Modify by Yuxj20181023
    ''' Modify by Yuxj20181129
    ''' 件号描述从jbst_sync_mes中T_WLMS获取
    ''' </summary>
    ''' <param name="mfgorder"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectErpMaterial(ByVal mfgorder As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetCollectErpMaterial
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT cc.mfgordername,cc.productname,cc.productbatchno,cc.qty firstassembleqty,cc.uom,jsm.t_wlms description ")
        sb.AppendFormat("FROM Collecterpmaterial cc ")
        sb.AppendLine("LEFT JOIN mfgorder m ON cc.mfgordername=m.mfgordername ")
        sb.AppendLine("INNER JOIN enginepartplan ep ON m.mfgorderid=ep.mfgorderid ")
        sb.AppendLine("LEFT JOIN productbase pb ON cc.productname=pb.productname ")
        sb.AppendLine(" left join jbst_sync_mes jsm on jsm.t_code=pb.productname")
        sb.AppendLine("LEFT JOIN product p ON p.productid=pb.revofrcdid  ")
        sb.AppendFormat(" WHERE cc.mfgordername='{0}'", mfgorder)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dt = dt.DefaultView.ToTable(True)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20181108 任务单整机打印方法
    ''' </summary>
    ''' <param name="MachineNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerNoByMachine(ByVal MachineNo As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetContainerNoByMachine
        Dim strSql As String =
            <string>
                SELECT ep.containerid,u.attributevalue,p.description,pb.productname,ep.containerno,wfs.workflowstepname,wfs.sequence FROM enginepartplan ep
                INNER JOIN mfgorder m ON ep.mfgorderid=m.mfgorderid
                LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN bom b ON b.workflowid=ep.workflowid                
                LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid
                WHERE ep.containerid IS NOT NULL AND u.userattributename='发动机'
                      AND bt.billtypename='大小件目录' 
            </string>

        Dim Sb As New StringBuilder()
        Sb.AppendLine(strSql)
        Sb.AppendFormat(" AND u.attributevalue='{0}' ", MachineNo)
        Sb.AppendLine(" AND EXISTS (SELECT 1 FROM issueerpmaterial iil WHERE iil.containerid=ep.containerid)")
        Sb.AppendLine("ORDER BY ep.containerno,wfs.sequence DESC ")

        'strSql += String.Format(" AND u.attributevalue='{0}' ", MachineNo)
        'strSql += " ORDER BY ep.containerno,wfs.sequence DESC "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(Sb.ToString)

        Dim Sb1 As New StringBuilder()
        Sb1.AppendLine(strSql)
        Sb1.AppendFormat(" AND u.attributevalue='{0}' ", MachineNo)
        Sb1.AppendLine(" AND NOT EXISTS (SELECT 1 FROM issueerpmaterial iil WHERE iil.containerid=ep.containerid)")
        Sb1.AppendLine("ORDER BY ep.containerno,wfs.sequence DESC ")
        Dim dt1 As DataTable = DatabaseHelper.Query_ReturnDatTable(Sb1.ToString)
        'dt = dt.DefaultView.ToTable(True)

        'Modify by Yuxj20181113 整机打印时有初检装的去掉一笔
        Dim ContainerNo As String = String.Empty
        Dim WfsName As String = String.Empty
        For i As Integer = dt.Rows.Count - 1 To 0 Step -1
            If ContainerNo = String.Empty Then
                ContainerNo = dt.Rows(i)("ContainerNo").ToString
                'WfsName = dt.Rows(i)("workflowstepname").ToString
            Else
                If (ContainerNo = dt.Rows(i)("ContainerNo").ToString) Then 'And (WfsName = dt.Rows(i)("workflowstepname").ToString)
                    dt.Rows.RemoveAt(i)
                Else
                    ContainerNo = dt.Rows(i)("ContainerNo").ToString
                End If
            End If
        Next
        dt.AcceptChanges()
        ContainerNo = String.Empty
        For i As Integer = dt1.Rows.Count - 1 To 0 Step -1
            If dt.Select("containerno='" + dt1.Rows(i)("ContainerNo") + "'").Length > 0 Then
                dt1.Rows.RemoveAt(i)
                Continue For
            End If

            If ContainerNo = String.Empty Then 'modify by longb 2022-06-30
                ContainerNo = dt1.Rows(i)("ContainerNo").ToString
            Else
                If (ContainerNo = dt1.Rows(i)("ContainerNo").ToString) Then 'And (WfsName = dt.Rows(i)("workflowstepname").ToString)
                    dt1.Rows.RemoveAt(i)
                Else
                    ContainerNo = dt1.Rows(i)("ContainerNo").ToString
                End If
            End If
        Next
        dt1.AcceptChanges()

        dt.Merge(dt1)

        Dim view = dt.DefaultView
        view.Sort = "containerno asc,sequence DESC"
        dt = view.ToTable(True)
        dt.AcceptChanges()
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20181108
    ''' 根据批次Id获取配套信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIssueErpMaterialbyContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetIssueErpMaterialbyContainer
        Dim sb As New StringBuilder
        Dim strSql As String =
            <string>
                SELECT im.containerno,p.description,pb.productname,pm.parttype,decode(pm.parttype,'0','大零件','1','小零件',pm.parttype) parttypedesc,  p1.description pdesc,cm.productbatchno,im.issueqty,im.issueerpmaterialid 
                FROM issueerpmaterial im
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                LEFT JOIN product p1 ON pm.productid=p1.productid
                LEFT JOIN productbase pb ON pb.productbaseid=p1.productbaseid
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                where isChange='0'               
            </string>
        sb.AppendLine(strSql)
        If para.ContainsKey("IdList") Then
            sb.AppendFormat(" and im.containerid in ('{0}')", para("IdList").Replace(",", "','"))
        End If
        sb.AppendLine(" order by im.containerno,pb.productname asc ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20181108
    ''' 配套件调整
    ''' </summary>
    ''' <param name="idList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateIssueErpMaterial(ByVal idList As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.UpdateIssueErpMaterial
        Dim strsql As String = String.Format(" update issueerpmaterial set isChange='1' where issueerpmaterialid in ('{0}')", idList.Replace(",", "','"))
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function
#Region "缺件查询"
    ''' <summary>
    ''' Modify by Yuxj20180723
    ''' 修改缺件查询逻辑，任务单只显示当前工作阶段的资料，缺件需包含报废的数量
    ''' Modify by Yuxj20180926 修改缺件逻辑，没有库存，有零件目录也算缺件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetLackQty(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetLackQty
        Dim strsql As String =
            <string>
                SELECT  u.attributevalue machine,mfg.mfgordername,p1.description CompDesc, pb.productname,
                        p.productid,p.description, pm.firstassembleqty requestqty,issueqty,
                        pm.firstassembleqty-nvl(issueqty,0) lackqty,ep.containerno togetherflag,
                        wfs.workflowstepid,wfs.workflowstepname
                 FROM enginepartplan ep
                 LEFT JOIN product p1 ON ep.productid=p1.productid 
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                INNER JOIN 
                (SELECT e.containerno,MIN(wfs.sequence) wfsseq FROM enginepartplan e
                LEFT JOIN bom b ON e.workflowid=b.workflowid
                LEFT JOIN billtype bt ON bt.billtypeid=b.billtypeid
                LEFT JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
                WHERE e.usecompleted='0'  AND bt.billtypename='大小件目录' AND e.containerid IS NOT NULL 
                GROUP BY e.containerno
                ) ep1 ON ep.containerno=ep1.containerno AND wfs.sequence=ep1.wfsseq
                LEFT JOIN mfgorder mfg ON ep.mfgorderid=mfg.mfgorderid
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid
                LEFT JOIN partmenuinfo pm ON ep.containerno=pm.containerno
                LEFT JOIN product p ON pm.productid=p.productid 
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid 
                LEFT JOIN (
                   SELECT im.partmenuinfoid,im.containerid,SUM(im.issueqty-NVL(isc.lossqty,0)) issueqty FROM
                   issueerpmaterial im 
                   LEFT JOIN (SELECT isc.issueerpmaterialid,SUM(isc.lossqty) lossqty  
                   FROM issueerpmaterialscrapinfo isc GROUP BY isc.issueerpmaterialid) isc
                   ON im.issueerpmaterialid=isc.issueerpmaterialid 
                   where im.ischange='0'
                   GROUP BY im.partmenuinfoid,im.containerid) im ON pm.partmenuinfoid=im.partmenuinfoid
                WHERE u.userattributename='发动机' 
                /*
                AND im.containerid IN
                (
                SELECT DISTINCT im.containerid  FROM issueerpmaterial im 
                INNER JOIN (SELECT im.containerno,MAX(im.createdate) createdate  FROM issueerpmaterial im where im.ischange='0' GROUP BY im.containerno) imn
                ON im.containerno=imn.containerno AND im.createdate=imn.createdate
                )
                */
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If
        If para.ContainsKey("Machine") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
        End If

        If para.ContainsKey("ContainerNo") Then
            'Add by Yuxj 20180226 因为任务单可能只是有零件目录，但没有发料 故不能取im.ContainerNo
            'sb.AppendFormat(" and im.ContainerNo='{0}' ", para("ContainerNo"))
            sb.AppendFormat(" and ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If
        sb.AppendLine(" AND pm.firstassembleqty-nvl(issueqty,0)>0 ")

        'Modify by Yuxj20181009 增加大小件区分
        If para.ContainsKey("PartType") Then
            sb.AppendFormat(" and pm.PartType = '{0}' ", para("PartType"))
        End If

        sb.AppendLine(" order by mfg.mfgorderName,pb.productname asc ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetLackQty = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
#End Region

#Region "多订单缺件查询"
    Function GetMuliLackQty(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetMuliLackQty
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT u.attributevalue Machine,mfg.mfgordername,c.togetherflag,pb.productname,p.description FROM Userattribute u ")
            .AppendLine("LEFT JOIN mfgorder mfg ON u.parentid=mfg.mfgorderid ")
            .AppendLine("LEFT JOIN container c ON mfg.mfgorderid=c.mfgorderid ")
            .AppendLine("LEFT JOIN product p ON c.productid=p.productid ")
            .AppendLine("LEFT JOIN productbase pb ON  p.productid=pb.revofrcdid ")
            .AppendLine("WHERE c.status='1' AND u.userattributename='发动机' ")
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" and u.attributevalue between '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" and u.attributevalue = '{0}' ", para("MachineStart"))
            End If
            .AppendLine("GROUP BY u.attributevalue,mfg.mfgordername,c.togetherflag,pb.productname,p.description")
        End With
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetMuliLackQty = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    Function GetMuliLackDetail(ByVal ContainerNolist As String, ByVal strMfgOrderList As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetMuliLackDetail
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT pm.productid,pb.productname ,p.description,sum(pm.firstassembleqty) requestqty,Sum(issueqty) issueqty,sum(pm.firstassembleqty-NVL(issueqty,0)) lackqty  ")
            .AppendLine("FROM partmenuinfo pm LEFT JOIN product p ON pm.productid=p.productid ")
            .AppendLine(" LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
            .AppendLine("LEFT JOIN ")
            .AppendLine("(SELECT partmenuinfoid,nvl(SUM(im.issueqty),0) issueqty from issueerpmaterial im   ")
            .AppendFormat("WHERE containerno in ('{0}')", ContainerNolist.Replace(",", "','"))
            .AppendLine("GROUP BY partmenuinfoid ) im ON pm.partmenuinfoid=im.partmenuinfoid ")
            .AppendFormat("where pm.containerno in ('{0}') ", ContainerNolist.Replace(",", "','"))
            .AppendLine("AND pm.firstassembleqty-NVL(issueqty,0)>0 group by pm.productid,pb.productname,p.description  ")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

#End Region

#Region "集件及开封管理"
    ''' <summary>
    ''' Add by Yuxj
    ''' Modify by Yuxj20190625 集件去封页面将任务单和零件分卡
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectContainerNo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueERPMaterialIbusiness.GetCollectContainerNo
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                          SELECT u.attributevalue MachineName,im.containerno togetherflag,im.containerid,
                          pb.productname,p.description,wfs.workflowstepname,wfs.workflowid
                          FROM ISSUEERPMATERIAL IM  
                          LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                          LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid
                          LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                          LEFT JOIN product p ON p.productid=ep.productid
                          LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                          LEFT JOIN Userattribute u ON ep.mfgorderid=u.parentid 
                          LEFT JOIN (SELECT IMC.ISSUEERPMATERIALID, SUM(NVL(IMC.CONFIRMQTY, 0)) CONFIRMQTY
                                       FROM ISSUEERPMATERIALCONFIRMATION IMC
                                      GROUP BY IMC.ISSUEERPMATERIALID) IMC
                            ON IMC.ISSUEERPMATERIALID = IM.ISSUEERPMATERIALID
                      </string>)

        sb.AppendLine("where im.issueqty>0 and im.ischange='0'  and u.userattributename='发动机' ")
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
        End If
        If para.ContainsKey("PartType") Then
            sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
        End If
        If para.ContainsKey("Machine") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
        End If

        If para.ContainsKey("Status") Then
            If para("Status") = "0" Then
                sb.AppendLine("and nvl(imc.confirmqty,0)<im.issueqty ")
            ElseIf para("Status") = "1" Then
                sb.AppendLine("and nvl(imc.confirmqty,0)>=im.issueqty ")
            Else

            End If
        End If

        sb.AppendLine("GROUP BY u.attributevalue,im.containerno,im.containerid,")
        sb.AppendLine(" pb.productname,p.description,wfs.workflowstepname,wfs.workflowid ")

        sb.AppendLine("Order by u.attributevalue,im.containerno asc")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetCollectContainerNo = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' Add by Yuxj
    ''' Modify by Yuxj20190626 增加部件名称，零件名称列
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectIssue(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetCollectIssue
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT c.togetherflag,c.containername, im.issueerpmaterialid, cm.productname,cm.productbatchno,pm.firstassembleqty,im.issueqty,nvl(SUM(imc.confirmqty),0) totalCollectQty ")
        sb.AppendLine(", wfs.workflowstepname, u.attributevalue MachineName,c.containerid,wfs.workflowid ")
        'Modify by Yuxj20180621
        sb.AppendLine(" ,p.description productdesc,p1.description togetherdesc ")
        sb.AppendLine(" FROM issueerpmaterial im ")
        'Modify by Yuxj20181016
        sb.AppendLine(" LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")

        sb.AppendLine("LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
        sb.AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("LEFT JOIN product p ON pm.productid=p.productid ")
        sb.AppendLine("LEFT JOIN IssueERPMaterialConfirmation imc ON im.issueerpmaterialid=imc.issueerpmaterialid ")
        sb.AppendLine("left join container c ON c.containerid=im.containerid ")
        sb.AppendLine("LEFT JOIN product p1 ON c.productid=p1.productid ")
        sb.AppendFormat("LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid  ")
        sb.AppendFormat(" LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid ")
        sb.AppendLine("where im.issueqty>0 and  c.status='1' and u.userattributename='发动机' ")
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("ContainerNoList") Then
            sb.AppendFormat(" and im.ContainerNo in ('{0}') ", para("ContainerNoList").Replace(",", "','"))
        End If

        If para.ContainsKey("PartType") Then
            sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
        End If
        If para.ContainsKey("Machine") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
        End If

        If para.ContainsKey("Status") Then
            If para("Status") = "0" Then
                sb.AppendLine("HAVING nvl(SUM(imc.confirmqty),0)<im.issueqty ")
            ElseIf para("Status") = "1" Then
                sb.AppendLine("HAVING nvl(SUM(imc.confirmqty),0)>=im.issueqty ")
            Else

            End If
        End If

        sb.AppendLine("GROUP BY wfs.workflowid,wfs.workflowstepname,c.containerid,u.attributevalue,c.togetherflag,c.containername, im.issueerpmaterialid,cm.productname,cm.productbatchno,pm.firstassembleqty,im.issueqty ")
        sb.AppendLine(",p.description,p1.description ")
        sb.AppendLine("Order by u.attributevalue,c.togetherflag, cm.productname,cm.productbatchno,im.issueerpmaterialid asc")
        'Dim paraPage As New uMESPagingDataDTO
        'paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        'paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        'paraPage.strSQL = sb.ToString
        'GetCollectIssue = DatabaseHelper.RunProcedureForPage(paraPage)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 插入资料表 IssueERPMaterialConfirmation
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertIssueERPMaterialConfirmation(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertIssueERPMaterialConfirmation
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO issueerpmaterialconfirmation ")
        sb.AppendLine("(confirmqty, createdate, employeeid, issueerpmaterialconfirmationid, issueerpmaterialid, note) ")
        sb.AppendLine(" VALUES( ")
        sb.AppendFormat("'{0}',sysdate,'{1}','{2}','{3}',null ", para("confirmqty"), para("employeeid"), para("cid"), para("eid"))
        sb.AppendLine(" ) ")
        DatabaseHelper.ExecuteSql(sb.ToString)
        Return True
    End Function
#End Region

#Region "检验报废使用"
    ''' <summary>
    ''' 插入报废表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="ZSFlag"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertIssueERPMaterialScrapInfo(ByVal para As Dictionary(Of String, String), ByVal ZSFlag As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertIssueERPMaterialScrapInfo
        Dim i_lengh As Integer = 0
        If ZSFlag = "ZS" Then
            'Modify by Yuxj增加存储Container
            i_lengh = 11
        Else
            i_lengh = 7
        End If

        Dim strSql As String = String.Empty
        strSql = "INSERT INTO ISSUEERPMATERIALSCRAPINFO(CHECKERID,CHECKNOTE,COLLECTERPMATERIALID,CREATEDATE, " &
                                  " ISSUEERPMATERIALID,ISSUEERPMATERIALSCRAPINFOID,LOSSQTY,LOSSREASONID,WORKERID)"
        strSql += "VALUES(:CHECKERID,:CHECKNOTE,:COLLECTERPMATERIALID,SYSDATE,:ISSUEERPMATERIALID,:ISSUEERPMATERIALSCRAPINFOID," &
                            ":LOSSQTY,:LOSSREASONID,:WORKERID)"

        If ZSFlag = "ZS" Then
            strSql = "INSERT INTO ISSUEERPMATERIALSCRAPINFO(CHECKERID,CHECKNOTE,COLLECTERPMATERIALID,CREATEDATE, " &
                                  " ISSUEERPMATERIALID,ISSUEERPMATERIALSCRAPINFOID,LOSSQTY,LOSSREASONID,WORKERID,WorkReportMaterialRecordID,DismantleID,DismantleDate," &
                                  "Containerid,Workflowstepid,Stepinfoid)"
            strSql += "VALUES(:CHECKERID,:CHECKNOTE,:COLLECTERPMATERIALID,SYSDATE,:ISSUEERPMATERIALID,:ISSUEERPMATERIALSCRAPINFOID," &
                                ":LOSSQTY,:LOSSREASONID,:WORKERID,:WorkReportMaterialRecordID,null,null,:Containerid,:Workflowstepid,:Stepinfoid)"
        End If
        Dim insertPara(i_lengh) As OracleParameter
        'modify tianFW 2017/10/10 
        insertPara(0) = DatabaseHelper.NewParameter(":CHECKERID", para("CheckerID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":CHECKNOTE", para("CheckNote"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":COLLECTERPMATERIALID", para("CollectErpMaterialID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALSCRAPINFOID", Guid.NewGuid().ToString, OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":LOSSQTY", para("LossQty"), OracleType.Number)
        insertPara(6) = DatabaseHelper.NewParameter(":LOSSREASONID", para("LossReasonID"), OracleType.VarChar)
        insertPara(7) = DatabaseHelper.NewParameter(":WORKERID", para("WorkerID"), OracleType.VarChar)
        If ZSFlag = "ZS" Then
            insertPara(8) = DatabaseHelper.NewParameter(":WorkReportMaterialRecordID", para("WorkReportMaterialRecordID"), OracleType.VarChar)
            'Add by Yuxj20180612增加记录项
            insertPara(9) = DatabaseHelper.NewParameter(":Containerid", para("Containerid"), OracleType.VarChar)
            insertPara(10) = DatabaseHelper.NewParameter(":Workflowstepid", para("Workflowstepid"), OracleType.VarChar)
            insertPara(11) = DatabaseHelper.NewParameter(":Stepinfoid", para("Stepinfoid"), OracleType.VarChar)
        End If

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            InsertIssueERPMaterialScrapInfo = True
            Exit Function
        End If
        InsertIssueERPMaterialScrapInfo = False
    End Function

    ''' <summary>
    ''' add by Yuxj20180409 获取装试检验弹窗装配信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkMaterialRecord(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetWorkMaterialRecord
        '思路，因为串件单，周转单，排故单和任务单对应的零件图号为多对一关系
        '故先查询装配记录，然后根据零件号,任务单号和工步填补资料
        Dim dt As New DataTable
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT wk.qty,im.issueqty,pm.firstassembleqty,cm.productbatchno,cm.productname,wk.stepinfoid,sfb.stepinfoname, ")
        sb.AppendLine("cm.CollectErpMaterialID,im.IssueErpMaterialID,wk.WorkReportMaterialRecordID,isc.lossqty,")
        'sb.AppendLine("c.containername,m.mfgordername,pf.productfamilyname,wfs.workflowstepname,wc.workcentername,")
        sb.AppendLine("pm.productid,wk.containerid,DECODE(ex.billtype,'串件单','是','否') isC,DECODE(ex.billtype,'周转单','是','否') isZ ")
        sb.AppendLine("FROM Workreportmaterialrecord wk ")

        '统计报废信息
        sb.AppendLine("  LEFT JOIN  ")
        sb.AppendLine(" (SELECT sum(isc.lossqty) lossqty,isc.workreportmaterialrecordid FROM  issueerpmaterialscrapinfo isc GROUP BY workreportmaterialrecordid) isc ")
        sb.AppendLine(" ON wk.workreportmaterialrecordid=isc.workreportmaterialrecordid ")

        sb.AppendLine("LEFT JOIN issueerpmaterial im ON wk.issueerpmaterialid=im.issueerpmaterialid and im.ischange='0' ")
        sb.AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("LEFT JOIN Collecterpmaterial cm on cm.collecterpmaterialid=im.collecterpmaterialid ")
        sb.AppendLine("LEFT JOIN stepinfo sf ON wk.stepinfoid=sf.stepinfoid ")
        sb.AppendLine("LEFT JOIN stepinfobase sfb ON sf.stepinfobaseid=sfb.stepinfobaseid ")

        '暂时先用
        sb.AppendLine("LEFT JOIN Exchangebill ex ON ex.containerid=wk.containerid AND ex.comcontainername=cm.productbatchno ")
        sb.AppendFormat("where wk.containerid='{0}' ", para("Cid"))
        sb.AppendFormat("and wk.stepinfoid in ('{0}')", para("StepInfoList").Replace(",", "','"))
        dt = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        GetWorkMaterialRecord = dt
        ''获取串件单，周转单信息
        'sb.AppendLine("LEFT JOIN (SELECT * FROM  ")
        'sb.AppendLine("(SELECT ex.exchangeid,ex.billtype,ex.containerid,ex.productid  FROM exchangebill ex  ")
        'sb.AppendFormat("WHERE ex.containerid='{0}'", para("Cid"))
        'sb.AppendLine(" ORDER BY ex.operatedate DESC) WHERE ROWNUM=1) ")
        'sb.AppendLine("exx ON exx.containerid=wk.containerid and ex.productid")
        '获取排故单信息
        'sb.AppendLine("LEFT JOIN (SELECT COUNT(1) iCount FROM dealfaultbill  ")
        'sb.AppendFormat("WHERE ex.containerid='{0}' and stepinfoid='{1}')", para("Cid"), para("StepInfoId"))

    End Function

    ''' <summary>
    ''' 获取串件单周转单列表 modify by tianFW 修改为根据contianerno来查询所有的串件周转信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetExchangeBillData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetExchangeBillData
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT ex.exchangename,ex.exchangeid,pb.productname,")
        sb.AppendLine("DECODE(ex.billtype,'串件单','串件单','周转单'，'周转单'，'无') exchangetype")
        sb.AppendLine("FROM exchangebill ex ")
        sb.AppendLine("LEFT JOIN product p ON p.productid = ex.productid")
        sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        sb.AppendFormat("WHERE ex.isuse=0 AND ex.stepinfoid is null AND ex.containerno='{0}' ", para("containerNO"))
        sb.AppendLine(" ORDER BY p.productid,ex.operatedate DESC ")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 更新串件单周转单信息 create by tianFW 2018年5月25日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateExchangeBillData(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueERPMaterialIbusiness.UpdateExchangeBillData
        Dim sql As New StringBuilder
        sql.AppendFormat("UPDATE exchangebill eb SET eb.stepinfoid='{0}',eb.billstatus=1", para("StepinfoID"))
        sql.AppendFormat("WHERE eb.exchangeid='{0}'", para("ExchangeID"))
        If DatabaseHelper.ExecuteSql(sql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取装试不合格审理弹窗内容
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgorderInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetMfgorderInfo
        '不合格审理申请使用
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT c.containername,m.mfgordername,pf.productfamilyname,p.materialtrademark,u.attributevalue Machine FROM container c ")
        sb.AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")

        'Modify by Yuxj20180604重新获取批次对应机型
        sb.AppendLine("LEFT JOIN productbase pb1 ON m.productbaseid=pb1.productbaseid ")
        sb.AppendLine(" LEFT JOIN product p1 ON m.productid=p1.productid ")
        sb.AppendLine("LEFT JOIN product p2 ON p2.productid=NVL(pb1.revofrcdid,p1.productid) ")
        sb.AppendLine("LEFT JOIN productbase pb2 ON pb2.productbaseid=p2.productbaseid ")
        sb.AppendLine("LEFT JOIN product p ON c.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productworkflowmapping pww ON p.productid=pww.productid ")
        sb.AppendLine("INNER JOIN Workflow wf ON p.workflowid=wf.workflowid AND wf.ly2modle_node=pb2.productname ")
        sb.AppendLine("LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid=wf.workflowid ")
        sb.AppendLine("LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid ")
        'sb.AppendLine("LEFT JOIN product p ON c.productid=p.productid ")
        'sb.AppendLine("LEFT JOIN productfamily pf ON p.productfamilyid=pf.productfamilyid ")

        sb.AppendLine("LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid AND u.userattributename='发动机' ")
        sb.AppendFormat("where c.containerid='{0}' ", para("Cid"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
#End Region

#Region "任务单打印"

    ''' <summary>
    '''  获取任务单打印信息 add YangJiang 20180508
    ''' SmallPartType:1 检装目录，其他 初装
    ''' Modify by Yuxj20181129 
    ''' 件号描述从jbst_sync_mes中T_WLMS获取
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <param name="parttype"></param>
    ''' <param name="SmallPartType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTaskBillPrintInfo(ByVal ContainerID As String, ByVal parttype As String, ByVal SmallPartType As String) As System.Data.DataTable Implements IBusiness.uLMSIssueERPMaterialIbusiness.GetTaskBillPrintInfo
        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT  DECODE(pm.firstassembleqty-(im.issueqty-nvl(im.lossqty,0)),0,im.productbatchno,im.productbatchno||'='||(im.issueqty-NVL(im.lossqty,0))) productbatchno,im.productname,jsm.t_wlms description ")
        'add YangJiang 20180606 小件目录区分初装和检装
        If SmallPartType = "1" Then
            strSQL.AppendLine(",SUM(pm.checkassembleqty) firstassembleqty") 'modify by longb 2022-07-18
        Else
            strSQL.AppendLine(",SUM(pm.firstassembleqty) firstassembleqty") 'modify by longb 2022-07-18
        End If
        strSQL.AppendLine(" FROM (     SELECT im.containerid,im.containerno,im.partmenuinfoid,cm.productbatchno,cm.productname,SUM(NVL(im.issueqty,0)) issueqty,SUM(ip.lossqty) lossqty")
        strSQL.AppendLine("  FROM issueerpmaterial im   INNER JOIN collecterpmaterial cm ON cm.collecterpmaterialid=im.collecterpmaterialid")
        strSQL.AppendLine("LEFT JOIN (SELECT ip.issueerpmaterialid,SUM(ip.lossqty) lossqty FROM issueerpmaterialscrapinfo ip GROUP BY ip.issueerpmaterialid) ip ")
        strSQL.AppendLine(" ON im.issueerpmaterialid=ip.issueerpmaterialid")
        strSQL.AppendFormat(" WHERE im.containerid='{0}' and im.ischange='0'", ContainerID)
        strSQL.AppendLine(" GROUP BY im.containerid,im.containerno,im.partmenuinfoid,cm.productbatchno,cm.productname ) im")
        strSQL.AppendLine(" LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid")
        strSQL.AppendLine(" LEFT JOIN product p ON pm.productid=p.productid")
        'Modify by Yuxj20181129
        strSQL.AppendLine(" left join jbst_sync_mes jsm on jsm.t_code=im.productname ")

        strSQL.AppendFormat(" WHERE im.containerid='{0}'  ", ContainerID)
        strSQL.AppendFormat(" AND pm.parttype='{0}'", parttype)
        'add YangJiang 20180606 小件目录区分初装和检装
        If SmallPartType = "1" Then
            strSQL.AppendFormat(" AND pm.checkassembleqty>0")
        ElseIf SmallPartType = "2" Then
            strSQL.AppendFormat(" AND pm.firstassembleqty>0")
        End If
        strSQL.AppendLine(" and im.issueqty-NVL(im.lossqty,0)>0 ")
        strSQL.AppendLine(" GROUP BY DECODE(pm.firstassembleqty-(im.issueqty-nvl(im.lossqty,0)),0,im.productbatchno,im.productbatchno||'='||(im.issueqty-NVL(im.lossqty,0))), im.productname,jsm.t_wlms") 'add by longb 2022-07-18
        strSQL.AppendLine(" ORDER BY im.productname,productbatchno asc")
        'Modify by Yuxj20180703 没有配套的件号也需打印
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)

        Dim dtProduct As DataTable = dt.DefaultView.ToTable(True, "ProductName")
        Dim strProductList As String = String.Empty
        For Each dr As DataRow In dtProduct.Rows
            strProductList += dr("productName").ToString + ","
        Next


        strSQL = New StringBuilder
        With strSQL
            .AppendLine(" SELECT  null productbatchno,pb.productname,jsm.t_wlms description ")
            If SmallPartType = "1" Then
                .AppendLine(",SUM(pm.checkassembleqty) firstassembleqty") 'modify by longb 2022-07-18
            Else
                .AppendLine(",SUM(pm.firstassembleqty) firstassembleqty") 'modify by longb 2022-07-18
            End If
            .AppendLine("FROM partmenuinfo pm ")
            .AppendLine("LEFT JOIN container c ON pm.containerno=c.togetherflag ")
            .AppendLine("LEFT JOIN product p ON pm.productid=p.productid ")
            .AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
            .AppendLine("left join jbst_sync_mes jsm on jsm.t_code=pb.productname")
            .AppendFormat("WHERE c.containerid='{0}' and c.status='1'", ContainerID)
            If strProductList.Length > 0 Then
                strProductList = strProductList.Substring(0, strProductList.Length - 1)
                .AppendFormat(" and pb.productname not in ('{0}')", strProductList.Replace(",", "','"))
            End If

            .AppendFormat(" AND pm.parttype='{0}' GROUP BY pb.productname,jsm.t_wlms ", parttype) 'modify by longb 2022-07-18
        End With
        Dim dtPartMenu As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
        dt.Merge(dtPartMenu)
        Dim dv As DataView = dt.DefaultView
        dv.Sort = "ProductName, productbatchno asc "
        dt = dv.ToTable


        GetTaskBillPrintInfo = dt
    End Function

#End Region
#Region "故检报废弹窗"
    ''' <summary>
    ''' Add by Yuxj 20180531 根据故检任务单获取发动机所有件号
    ''' Modify by Yuxj20180718增加件号名称查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetMfgProduct
        Dim sb As New StringBuilder
        With sb
            .AppendLine(" SELECT cm.productname,pm.productid,pm.firstassembleqty qtyrequired,p.description pdesc  FROM issueerpmaterial im ")
            .AppendLine(" LEFT JOIN container c1  ON im.containerid=c1.containerid and c1.status='1'")
            .AppendLine(" LEFT JOIN container c ON c1.mfgorderid=c.mfgorderid")
            .AppendLine(" LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid")
            .AppendLine(" LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid")
            'add YangJiang 20180718 增加按照零件名称查询 Modify by Yuxj20181213
            .AppendLine("LEFT JOIN product p ON p.productid=pm.productid ")
            .AppendLine("LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
            '.AppendLine(" LEFT JOIN productbase pb ON pb.productname=cm.productname")
            '.AppendLine(" LEFT JOIN product p ON p.productbaseid=pb.productbaseid")
        End With
        sb.AppendFormat(" where c.containerid='{0}' and c.status='1' and im.ischange='0'", para("ContainerId"))
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and cm.productName like '%{0}%'", para("ProductName"))
        End If
        'add YangJiang 20180718 增加按照零件名称查询
        If para.ContainsKey("Description") Then
            sb.AppendFormat(" and p.description like '%{0}%'", para("Description"))
        End If
        sb.AppendLine(" Group by cm.productname,pm.productid,pm.firstassembleqty,p.description ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 获取装配整合信息 create tianFW 2018年5月9日
    ''' 故检获取故检前最近阶段配套的批次配套信息Modify by Yuxj20180613
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetGJIssueMaterialIntegratedData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetGJIssueMaterialIntegratedData
        'Modify by Yuxj20180613 调整故检获取配套信息的逻辑
        'Modify by Yuxj20180628 故检本身不配套，取本发动机此件号所有任务单最近一个阶段的配套信息
        Dim Cid As String = String.Empty
        Dim sb As New StringBuilder
        sb.AppendFormat("  select togetherflag,m.mfgorderid,m.mfgordername from container c  ")
        sb.AppendFormat("  LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
        sb.AppendFormat(" where c.containerid='{0}' and c.status='1'", para("ContainerID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim ContainerNo As String = dt.Rows(0)("togetherflag")
        Dim MfgOrderId As String = dt.Rows(0)("MfgOrderId")
        Dim MfgOrderName As String = dt.Rows(0)("MfgOrderName")

        'Dim dtIssue As DataTable = GetLastIssueContainerDT(ContainerNo, Cid)
        'If Cid = String.Empty Then
        '    '任务单没有配套信息
        '    GetGJIssueMaterialIntegratedData = New DataTable
        '    Exit Function
        'End If
        sb = New StringBuilder
        With sb
            .AppendLine("SELECT im.issueerpmaterialid,cm.collecterpmaterialid,pm.partmenuinfoid,nvl(im.issueqty,0) issueqty,")
            .AppendLine("cm.productname, cm.productbatchno,b.confirmqty,im.createdate,nvl(a.lossqty,0) lossqty,")
            .AppendLine("DECODE(ex.billtype,'串件单','串件单','周转单'，'周转单'，'无') isCZ,ex.exchangeid")
            .AppendLine(" ,CASE WHEN d.issueerpmaterialid IS NULL AND dd.issueerpmaterialid IS NULL THEN '未排故' ELSE '已排故' END DealStatus") 'add YangJiang 20180719
            .AppendLine("FROM issueerpmaterial im")
            .AppendLine("LEFT JOIN (SELECT SUM(nvl(imsi.lossqty,0)) lossqty, imsi.issueerpmaterialid")
            .AppendLine("           FROM issueerpmaterialscrapinfo imsi GROUP BY imsi.issueerpmaterialid) a")
            .AppendLine("ON a.issueerpmaterialid = im.issueerpmaterialid")
            .AppendLine("LEFT JOIN (SELECT SUM(imc.confirmqty) confirmqty, imc.issueerpmaterialid")
            .AppendLine("           FROM issueerpmaterialconfirmation imc GROUP BY imc.issueerpmaterialid) b")
            .AppendLine("ON b.issueerpmaterialid = im.issueerpmaterialid")
            .AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid = pm.partmenuinfoid")
            .AppendLine("LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid = cm.collecterpmaterialid")
            .AppendLine("LEFT JOIN Exchangebill ex ON ex.containerid = im.containerid AND ex.comcontainername=cm.productbatchno")
            'add YangJiang 20180719判断是否排故
            .AppendLine("LEFT JOIN dealfaultbill d ON d.issueerpmaterialid=im.issueerpmaterialid")
            .AppendLine("LEFT JOIN dealfaultbilldetail dd ON dd.issueerpmaterialid=im.issueerpmaterialid")

            .AppendLine("where b.confirmqty>0 and im.ischange='0'")
            'Modify by Yuxj20180628
            'If para.ContainsKey("ContainerID") Then
            '    .AppendFormat(" and im.containerid in (select containerid from container c where c.mfgorderid in ")
            '    .AppendFormat(" (select mfgorderid from container where containerid='{0}'))", para("ContainerID"))
            'End If
            .AppendLine(" and im.containerid in ")
            .AppendLine(" ( ")
            .AppendLine(" SELECT DISTINCT im.containerid FROM issueerpmaterial im WHERE (im.containerno,im.createdate) IN ")
            .AppendLine(" ( SELECT im.containerno,MAX(im.createdate)  FROM issueerpmaterial im ")
            .AppendLine("  LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
            .AppendFormat(" WHERE cm.mfgordername='{0}' AND cm.productname='{1}' and im.ischange='0'  GROUP BY im.containerno  )", MfgOrderName, para("ProductName"))
            .AppendLine(" ) ")

            If para.ContainsKey("ProductName") Then
                .AppendFormat(" and cm.productname='{0}'", para("ProductName"))
            End If

            .AppendFormat(" and cm.MfgOrderName='{0}'", MfgOrderName)
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function

    ''' <summary>
    ''' Add by Yuxj20181016
    ''' 判断是否需要插入工时表ContainerSteptimeSheet
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CheckInsertContainerSteptimeSheet(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueERPMaterialIbusiness.CheckInsertContainerSteptimeSheet
        CheckInsertContainerSteptimeSheet = False
        If para("FirstSecondIssue") = String.Empty Then
            Exit Function
        End If

        Dim WorkFlowStepId As String = String.Empty
        Dim StepInfoId As String = String.Empty
        Dim WorkFlowStepName As String = String.Empty
        Dim StepInfoName As String = String.Empty
        Dim Durationperunit As String = "0"
        Dim dt As DataTable = GetCollectWorkFlowData(para("WorkFlowId"), para("PartType"))
        If dt.Rows.Count > 0 Then
            If para("FirstSecondIssue") = "1" Then
                WorkFlowStepId = dt.Rows(0)("WorkFlowStepId").ToString
                StepInfoId = dt.Rows(0)("StepInfoId").ToString
                WorkFlowStepName = dt.Rows(0)("WorkFlowStepName").ToString
                StepInfoName = dt.Rows(0)("StepInfoName").ToString
                Durationperunit = dt.Rows(0)("Durationperunit").ToString
            ElseIf para("FirstSecondIssue") = "2" Then
                If dt.Rows.Count >= 1 Then
                    WorkFlowStepId = dt.Rows(0)("WorkFlowStepId").ToString
                    StepInfoId = dt.Rows(0)("StepInfoId").ToString
                    WorkFlowStepName = dt.Rows(0)("WorkFlowStepName").ToString
                    StepInfoName = dt.Rows(0)("StepInfoName").ToString
                    Durationperunit = dt.Rows(0)("Durationperunit").ToString
                End If
            End If

            If StepInfoId = String.Empty Then
                Exit Function
            End If

            '判断工时表资料是否存在
            Dim sb As New StringBuilder
            With sb
                .AppendLine("SELECT CS.* FROM containersteptimesheet CS ")
                .AppendLine("LEFT JOIN container c ON cs.containerid=c.containerid ")
                .AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
                .AppendLine("LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid ")
                .AppendLine("WHERE u.userattributename='发动机' and c.status='1'  ")
                .AppendFormat(" AND u.attributevalue='{0}' AND cs.Stepinfoid='{1}' AND cs.Workflowstepid='{2}'", para("MachineName"), StepInfoId, WorkFlowStepId)
            End With
            dt = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
            If dt.Rows.Count > 0 Then
                '发动机工时资料已经插入
                Exit Function
            End If

            '插入工时表ContainerSteptimeSheet
            sb = New StringBuilder
            sb.AppendLine(" INSERT INTO CONTAINERSTEPTIMESHEET")
            sb.AppendLine(" (createdate,workcenterid,workerid,teamid,containersteptimesheetid,workflowstepname,workflowstepid,Stepinfoid,containername,containerid, totalduration,FactoryID,Qty,SCRAPQTY) ")
            sb.AppendLine(" values ")
            sb.AppendFormat("(sysdate, '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',1,0)", para("WorkCenterId"), para("WorkerId"), para("TeamId"),
                             System.Guid.NewGuid.ToString, WorkFlowStepName, WorkFlowStepId, StepInfoId,
                             para("ContainerName"), para("ContainerId"), Durationperunit, para("FactoryId"))
            Dim row As Integer = DatabaseHelper.ExecuteSql(sb.ToString)
            CheckInsertContainerSteptimeSheet = True
        Else
            Exit Function
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20181017
    ''' 获取集件去封工步信息
    ''' </summary>
    ''' <param name="WorkFlowId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCollectWorkFlowData(ByVal WorkFlowId As String, ByVal PartType As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetCollectWorkFlowData
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT wfs.workflowid,wfs.workflowstepid,wfs.sequence,  si.Durationperunit , ")
            .AppendLine(" wfs.workflowstepname,sib.stepinfoname,si.Description,si.stepinfoid  FROM workflowstep wfs ")
            .AppendLine(" LEFT JOIN workflow wf ON wfs.workflowid=wf.workflowid ")
            .AppendLine(" LEFT JOIN stepinfo SI ON wfs.workflowstepid=si.Workflowstepid ")
            .AppendLine(" LEFT JOIN stepinfobase sib ON si.Stepinfobaseid=sib.stepinfobaseid ")
            .AppendFormat(" where wf.workflowid='{0}'  ", WorkFlowId)
            If PartType = "1" Then
                .AppendFormat(" and INSTR(si.Description,'集件')>0  ")
            Else
                .AppendFormat(" and INSTR(si.Description,'去封')>0 ")
            End If
            .AppendLine(" order by wfs.sequence asc ")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

#End Region

#Region "插入集件信息表"
    ''' <summary>
    ''' Add by Yuxj20180606插入集件信息表
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertCollectErpMaterial(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.InsertCollectErpMaterial
        InsertCollectErpMaterial = False
        Dim strsql As String =
            <string>
                INSERT INTO CollectErpMaterial
                    (collecterpmaterialid,createdate, productbatchno,productname,mfgordername, 
                     qty,uom,originalproductname,isautoplan,isnonconforming,iserp,sourceqty )
                VALUES
                    (:collecterpmaterialid,sysdate, :productbatchno,:productname,:mfgordername, 
                     :qty,:uom,:originalproductname,:isautoplan,:isnonconforming,:iserp,:sourceqty)
            </string>
        Dim insertpar(10) As OracleParameter
        '赋初值
        entity.OriginalProductName = String.Empty
        entity.CollectERPMaterialID = System.Guid.NewGuid.ToString
        entity.IsAutoPlan = String.Empty
        entity.IsNonConforming = String.Empty
        entity.IsErp = String.Empty
        insertpar(0) = DatabaseHelper.NewParameter(":collecterpmaterialid", entity.CollectERPMaterialID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":productbatchno", entity.ProductBatchno, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":productname", entity.ProductName, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":mfgordername", entity.MfgorderName, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":uom", entity.Uom, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":originalproductname", entity.OriginalProductName, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":isautoplan", entity.IsAutoPlan, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":isnonconforming", entity.IsNonConforming, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":iserp", entity.IsErp, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":sourceqty", entity.Qty, OracleType.VarChar) '保存原始数量
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断订单，批次，件号是否已经存在
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function BoolCollectErpMaterialExist(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.BoolCollectErpMaterialExist
        BoolCollectErpMaterialExist = False
        Dim sb As New StringBuilder
        sb.AppendLine("select * from collecterpMaterial cm  ")
        sb.AppendFormat("where mfgordername='{0}' ", entity.MfgorderName)
        sb.AppendFormat("and productbatchno='{0}' ", entity.ProductBatchno)
        sb.AppendFormat("and ProductName='{0}' ", entity.ProductName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断订单号是否存在
    ''' </summary>
    ''' <param name="mfgname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function boolMfgOrderExist(ByVal mfgname As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.boolMfgOrderExist
        boolMfgOrderExist = False
        Dim dt As DataTable =
            DatabaseHelper.Query_ReturnDatTable(String.Format("select 1 from mfgorder m where m.mfgordername='{0}'", mfgname))
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断件号是否存在
    ''' </summary>
    ''' <param name="Productname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function boolProductNameExist(ByVal Productname As String) As Boolean Implements uLMSIssueERPMaterialIbusiness.boolProductNameExist
        boolProductNameExist = False
        Dim dt As DataTable =
            DatabaseHelper.Query_ReturnDatTable(String.Format("select 1 from productbase pb where pb.productname='{0}'", Productname))
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 删除集件信息
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DeleteCollectErpMaterial(ByVal entity As BusinessRule.DTO.IssueERPMaterialDTO) As Boolean Implements uLMSIssueERPMaterialIbusiness.DeleteCollectErpMaterial
        Dim strsql As String =
            <string>
                delete from collecterpMaterial cm 
                where mfgordername=:mfgordername and productbatchno=:productbatchno and productname=:productname
            </string>
        Dim insertpar(2) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":mfgordername", entity.MfgorderName, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":productbatchno", entity.ProductBatchno, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":productname", entity.ProductName, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

#End Region

#Region "施工单集件信息"
    ''' <summary>
    ''' Add by Yuxj20181213 获取零件批次信息
    ''' </summary>
    ''' <param name="Batch"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIssueErpMaterialByBatch(ByVal Batch As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetBatchInfo
        Dim strsql As String = String.Format(" select cm.productbatchno,cm.productname,cm.mfgordername,cm.qty,cm.Collecterpmaterialid from Collecterpmaterial cm where cm.productbatchno='{0}' ", Batch)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取批次对应订单信息
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfoByContainerId(ByVal cid As String) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetContainerInfoByContainerId
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT c.containerid,c.containername,m.mfgordername,c.productid,c.mfgorderid,c.togetherflag FROM container c ")
        sb.AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
        sb.AppendFormat("where c.containerid='{0}' ", cid)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

#End Region

#Region "任务单配套优化"
    '获取没有配齐的数据
    Function GetIssueMaterialNotFinished(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetIssueMaterialNotFinished
        'add by longb 2024-03-12 根据订单件号名称判断包含（Z1:初装，Z2:检装）
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("V_CONTAINERID"))
        Dim columns As String = "pm.firstassembleqty"
        If strFirstSecondIssue.Equals("2") Then
            columns = "pm.checkassembleqty"
        End If

        Dim strsql As String =
           <string>
                SELECT pm.partmenuinfoid, pm.productid,pb.productname,{3} firstassembleqty,
                SUM(nvl(im.issueqty,0)) sumissueqty,{3}-SUM(nvl(im.issueqty,0)) skipqty,pm.SPECID,ws.WORKFLOWSTEPID  
                FROM partmenuinfo pm 
                LEFT JOIN issueerpmaterial im ON im.partmenuinfoid=pm.partmenuinfoid and im.containerid='{0}' 
                LEFT JOIN product p ON p.productid=pm.productid
                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                 LEFT JOIN (
                	SELECT s.SPECID,WFS.WORKFLOWSTEPID,wfs.workflowid FROM WORKFLOWSTEP WFS--add by longb 关联查询 WORKFLOWSTEPID
                	LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid
                	LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid
                	LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)
                	WHERE WFS.WORKFLOWID='{1}'
                ) ws ON ws.SPECID=pm.SPECID
                WHERE  pm.containerno='{2}' AND ws.workflowid='{1}'
                GROUP BY pm.partmenuinfoid,pm.productid,pb.productname,{3},pm.SPECID,ws.WORKFLOWSTEPID 
                HAVING {3}>SUM(nvl(im.issueqty,0)) 
            </string>
        strsql = String.Format(strsql, para("V_CONTAINERID"), para("WorkFlowId"), para("V_CONTAINERNO"), columns)
        Dim dtIssue As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dtIssue
    End Function

    '订单中未配完的物料
    Function GetCollectErpBufferData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueERPMaterialIbusiness.GetCollectErpBufferData
        Dim strsql As String =
        <string>
                SELECT cm.collecterpmaterialid, cm.productname,cm.productbatchno, cm.qty,SUM(nvl(im.issueqty,0)),
                cm.qty-SUM(nvl(im.issueqty,0)) stockqty 
                FROM Collecterpmaterial cm
                LEFT JOIN issueerpmaterial im ON cm.collecterpmaterialid=im.collecterpmaterialid
                WHERE cm.mfgordername='{0}' 
                GROUP BY cm.collecterpmaterialid, cm.productname,cm.productbatchno,cm.qty
                HAVING cm.qty>SUM(nvl(im.issueqty,0))
                ORDER BY cm.productname,cm.productbatchno ASC
            </string>
        strsql = String.Format(strsql, para("V_MFGORDER"))
        Dim dtCollectErpBuffer As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dtCollectErpBuffer
    End Function
#End Region

#Region "零组件交接"
    '配送人执行sql
    Function GetPartsAssembleSql(ByVal para As Dictionary(Of String, String)) As String Implements uLMSIssueERPMaterialIbusiness.GetPartsAssembleSql
        Dim strsql As New StringBuilder()
        strsql.AppendLine("UPDATE issueerpmaterial SET ")
        strsql.AppendFormat("ASSEMBLEEMPLOYEEID='{0}', ", para("ASSEMBLEEMPLOYEEID"))
        strsql.AppendLine("STATUS=1 ")
        strsql.AppendFormat("where IssueERPMaterialID='{0}' ", para("ISSUEERPMATERIALID"))
        Return strsql.ToString()
    End Function

    '接收人执行sql
    Function GetPartsAcceptSql(ByVal para As Dictionary(Of String, String)) As String Implements uLMSIssueERPMaterialIbusiness.GetPartsAcceptSql
        Dim strsql As New StringBuilder()
        strsql.AppendLine("UPDATE issueerpmaterial SET ")
        strsql.AppendFormat("ACCEPTEMPLOYEEID='{0}', ", para("ACCEPTEMPLOYEEID"))
        strsql.AppendLine("STATUS=2, ")
        strsql.AppendFormat("ACCEPTDATE={0} ", "sysdate")
        strsql.AppendFormat("where IssueERPMaterialID='{0}' ", para("ISSUEERPMATERIALID"))
        Return strsql.ToString()
    End Function

    '获取零组件状态(0,未配送;1,已配送;2,已接收)
    Function GetPartsStatus(ByVal ISSUEERPMATERIALID As String) As String Implements uLMSIssueERPMaterialIbusiness.GetPartsStatus
        Dim strsql As New StringBuilder()
        strsql.AppendLine("select STATUS from issueerpmaterial ")
        strsql.AppendFormat("where IssueERPMaterialID='{0}' ", ISSUEERPMATERIALID)
        Return DatabaseHelper.GetSingle(strsql.ToString)
    End Function

    '批量执行
    Function PartsReceptTran(ByVal sqlTran As ArrayList) As Boolean Implements uLMSIssueERPMaterialIbusiness.PartsReceptTran
        DatabaseHelper.ExecuteSqlListTran(sqlTran)
        Return True
    End Function
    '根据WorkFlowId、SpecId 获取 WorkFlowStepID -----add by longb 2022-05-19
    Function GetWorkFlowStepID(ByVal WorkFlowId As String, ByVal SpecId As String) As String Implements uLMSIssueERPMaterialIbusiness.GetWorkFlowStepID
        Dim strsql As New StringBuilder()
        strsql.AppendLine("SELECT WFS.WORKFLOWSTEPID FROM WORKFLOWSTEP WFS ")
        strsql.AppendLine("LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid ")
        strsql.AppendLine("LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid ")
        strsql.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid) ")
        strsql.AppendFormat("WHERE WFS.WORKFLOWID='{0}' AND s.specid='{1}' ", WorkFlowId, SpecId)
        Return DatabaseHelper.GetSingle(strsql.ToString)
    End Function

#End Region


End Class
